Is there a way to maintain currency formatting when within a formula?

Deena Duran

Hello all,

I want to use document generator to make annual compensation letters. Leadership wants the compensation dollar amounts to be formatted within paragraphs, which makes a traditional fillable form a little challenging when I need to be super precise about being in line with the rest of the paragraph. They want one of the paragraphs to read:

"For FY 2026, your compensation will be: Base Salary of $XXX,XXX.XX, Supplements of $XX,XXX.XX and Administrative B of $XXX,XXX.XX, for a total contract salary of $XXX,XXX.XX."

I created a column with the following formula, hoping to capture the paragraph above:

="For FY 2026, your compensation will be: Base Salary of " + [Base Contract Salary]@row + ", Supplements of " + [Total Supplemental Compensation]@row + " and Administrative B of " + [Administrative B]@row + ", for a total contract salary of " + [Total contract salary]@row + "."

However, the problem that I run into is that the currency formatting is not retained, even if I remove it on the original column and type in my dollar sign and commas manually.

Is there an alternate formula that I can use to accomplish this, or would it best be done by changing my PDF formatting? The other problem is that not everyone has each type of compensation, and it is much easer to adjust the above formula for the outliers rather than create additional Generate PDF automations for each possible combination.

Paul Newcome

I would suggest using helper columns as the formula to get a text string with the "$" and commas and whatnot out of a number is a bit lengthy.

But once you set up the helper columns for the amounts, you can reference the helper columns in a string formula the same way you did above.

The formula below will convert any positive number below 1 trillion into the needed format (I would suggest a find/replace to swap [Total Column]@row with whatever column name you are using in your sheet so that none get missed):

="$" + IF(LEN(INT(ABS([Total Column]@row))) <= 3, RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 6, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 9, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 8, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 12, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 11, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 9)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 8, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3))))) + IF([Total Column]@row - INT([Total Column]@row) = 0, ".00", RIGHT([Total Column]@row - INT([Total Column]@row), 3))

Paul Newcome

@Deena Duran Good catch! Here is how I modified it…

="$" + IF(LEN(INT(ABS([Total Column]@row ))) <= 3, RIGHT(INT(ABS([Total Column]@row )), 3), IF(LEN(INT(ABS([Total Column]@row ))) <= 6, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 5, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row )), 3), IF(LEN(INT(ABS([Total Column]@row ))) <= 9, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 8, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 5, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row )), 3), IF(LEN(INT(ABS([Total Column]@row ))) <= 12, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 11, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 9)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 8, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 5, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row )), 3))))) + SUBSTITUTE("." + MID(ABS([Total Column]@row - INT([Total Column]@row )) + "00", 2, 3), "..", ".")

Paul Newcome

I would suggest using helper columns as the formula to get a text string with the "$" and commas and whatnot out of a number is a bit lengthy.

But once you set up the helper columns for the amounts, you can reference the helper columns in a string formula the same way you did above.

The formula below will convert any positive number below 1 trillion into the needed format (I would suggest a find/replace to swap [Total Column]@row with whatever column name you are using in your sheet so that none get missed):

="$" + IF(LEN(INT(ABS([Total Column]@row))) <= 3, RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 6, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 9, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 8, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 12, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 11, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 9)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 8, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3))))) + IF([Total Column]@row - INT([Total Column]@row) = 0, ".00", RIGHT([Total Column]@row - INT([Total Column]@row), 3))

AdamSYNH

@Deena Duran

Hopefully someone has a less convoluted version than this, but you could split and then recombine the dollar amounts using several helper columns and some of the text formulas, forcing Smartsheet to recognize the values as text:

NB! Without the 0 helper column and simply using the LEFT/MID/RIGHT formulas you lose the trailing 0 in the highlighted row of the first screenshot below

0 Helper

=Dollars@row + 0.001
Length =LEN([0 Helper]@row) - 1
Cents =MID([0 Helper]@row, FIND(".", [0 Helper]@row, 1) + 1, 2)

Hundreds

=IF(Length@row = 8, MID(Dollars@row, 3, 3),
IF(Length@row = 9, MID(Dollars@row, 4, 3),
IF(Length@row = 10, MID(Dollars@row, 5, 3),
IF(Length@row = 11, MID(Dollars@row, 6, 3), ""))))"

Thousands

=IF(Length@row = 8, LEFT(Dollars@row, 2),
IF(Length@row = 9, LEFT(Dollars@row, 3),
IF(Length@row = 10, MID(Dollars@row, 2, 3),
IF(Length@row = 11, MID(Dollars@row, 3, 3), ""))))"

Millions

=IF(Length@row < 10, "",
IF(Length@row = 10, LEFT(Dollars@row, 1),
IF(Length@row = 11, LEFT(Dollars@row, 2), "")))"

Dollars as Text

=IF(Length@row > 9, "$" + Millions@row + "," + Thousands@row + "," + Hundreds@row + "." + Cents@row, "$" + Thousands@row + "," + Hundreds@row + "." + Cents@row)

Deena Duran

Thank you for your help, @AdamSYNH! Because I needed to do this for about 8 different currency calculations, it would have been a few too many helper columns. I tried the solution that Paul had shared so I could add fewer columns.

@Paul Newcome, Thank you for your help! I needed to adapt the formula a little; I noticed that if there was a trailing 0, it would not pull it over. For example, "$45,000.10" would display as "$45,000.1" which is a little funky for the salary letters I needed. I had ChatGPT help me with the minor revision (as well as find/replace when I would move on to the next column) and got the formula below:

="$" + IF(LEN(INT(ABS([Base Contract Salary]@row ))) <= 3, RIGHT(INT(ABS([Base Contract Salary]@row )), 3), IF(LEN(INT(ABS([Base Contract Salary]@row ))) <= 6, IFERROR(IFERROR(MID(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 5, 3), LEFT(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 3)), "") + "," + RIGHT(INT(ABS([Base Contract Salary]@row )), 3), IF(LEN(INT(ABS([Base Contract Salary]@row ))) <= 9, IFERROR(IFERROR(MID(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 8, 3), LEFT(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 5, 3), LEFT(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 3)), "") + "," + RIGHT(INT(ABS([Base Contract Salary]@row )), 3), IF(LEN(INT(ABS([Base Contract Salary]@row ))) <= 12, IFERROR(IFERROR(MID(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 11, 3), LEFT(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 9)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 8, 3), LEFT(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 5, 3), LEFT(INT(ABS([Base Contract Salary]@row )), LEN(INT(ABS([Base Contract Salary]@row ))) - 3)), "") + "," + RIGHT(INT(ABS([Base Contract Salary]@row )), 3))))) + "." + RIGHT("00" + VALUE(ROUND(([Base Contract Salary]@row - INT([Base Contract Salary]@row )) * 100, 0)), 2)

I wanted to include it so folks have it, in case they need that formula later!

Thank you both!

Paul Newcome

@Deena Duran Good catch! Here is how I modified it…

="$" + IF(LEN(INT(ABS([Total Column]@row ))) <= 3, RIGHT(INT(ABS([Total Column]@row )), 3), IF(LEN(INT(ABS([Total Column]@row ))) <= 6, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 5, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row )), 3), IF(LEN(INT(ABS([Total Column]@row ))) <= 9, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 8, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 5, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row )), 3), IF(LEN(INT(ABS([Total Column]@row ))) <= 12, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 11, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 9)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 8, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 5, 3), LEFT(INT(ABS([Total Column]@row )), LEN(INT(ABS([Total Column]@row ))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row )), 3))))) + SUBSTITUTE("." + MID(ABS([Total Column]@row - INT([Total Column]@row )) + "00", 2, 3), "..", ".")