Add a formatted number to a text string

I'm looking to formulate a cell that has the following statement:

"Our project this month's is $xxx [over / under] budget."

I have a cell that calculates the $ value of the monthly budget. I want to include that number in the text string I drafted above.

So if I have a cell that has $152,345, then I want to have another cell that states...

"Our project this month is $152,345 under budget."

If in the following month, if the calculated value is -$345,679, then I want the following statement:

"Our project this month is -$345,679 over budget."

Anyone know how to that?

Find more posts tagged with

Accepted answers

All comments

Dale Murphy

@Berto D

="Our project this month is " + [Cell with dollar value] + " under budget."

works in my region.

Berto D

This does capture the number, but the currency formatting is lost. This is what it looks like...

"Our project this month is 152345 under budget."

Rather than...

"Our project this month is $152,345 under budget"

I'm guessing that the formula recognizes only the digits in the source cell and not the overlaying formatting (the $ sign and the comma).

I wonder whether formatting in a different way the number in the source cell might help so that the reference [Cell with dollar value] will also pull the $ sigh and comma separator.

Or...

maybe I can include some formatting in pull reference? Rather than just...

"+ [Cell with dollar value] +"

I can have some character formatting included so that even though the reference merely pulls the digits from the source, it will "reapply" the currency formatting?

Am I over-thinking?

Leibel S

@Berto D

See below example, works up till 999,999,999.99

="Our project this month is $" + IF(Amount@row > 1000000, INT(Amount@row / 1000000) + "," + RIGHT(INT(Amount@row / 1000), 3) + "," + RIGHT(INT(Amount@row), 3), IF(Amount@row > 1000, INT(Amount@row / 1000) + "," + RIGHT(INT(Amount@row), 3), INT(Amount@row))) + IF(ROUNDDOWN(MOD(Amount@row, 1), 2) < 0.0001, ".00", IF(ROUNDDOWN(MOD(Amount@row, 1), 2) < 0.09999, ".0" + RIGHT(ROUNDDOWN(MOD(Amount@row, 1), 2), 1), IF(LEN(ROUNDDOWN(MOD(Amount@row, 1), 2)) = 3, RIGHT(ROUNDDOWN(MOD(Amount@row, 1), 2), 2) + "0", RIGHT(ROUNDDOWN(MOD(Amount@row, 1), 2), 3)))) + " over budget"

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.