Add a formatted number to a text string

Berto D
Berto D ✭✭
edited 07/05/22 in Formulas and Functions

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?

· Share on FacebookShare on Twitter

Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @Berto D

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

    works in my region.

    dm

    · Share on FacebookShare on Twitter
  • Berto D
    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?

    · Share on FacebookShare on Twitter
  • Leibel S
    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"

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!