Insert commas into formula dollar amount populated from another cell

Sandy Glassberg

I saw someone else asked the same question in another thread https://community.smartsheet.com/discussion/108384/including-a-comma-for-currency-within-an-existing-formula but the answer was very confusing and overwhelming. Hoping someone can help me with a simpler formula for my specific problem.

I currently have a formula that returns values from other cells, including a Currency column. But the formula returns that currency amount as a simple number without a dollar sign or commas. I've updated the formula to manually insert a dollar sign, but is there a way to insert commas? Screenshot included. Thank you!

Screenshot 2025-04-07 at 12.19.01.png

Paul Newcome

Take the below and drop it into your existing formula in place of [ROM Value]@row.

=IF(……………, " ROM: $" + insert the below here + " ("…………..)

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

The above will take care of any number less than 1 trillion.

.

Paul Newcome

This should take care of it:

IF(LEN(INT(ABS([ROM Value]@row))) <= 3, RIGHT(INT(ABS([ROM Value]@row)), 3), IF(LEN(INT(ABS([ROM Value]@row))) <= 6, IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 5, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 3)), "") + "," + RIGHT(INT(ABS([ROM Value]@row)), 3), IF(LEN(INT(ABS([ROM Value]@row))) <= 9, IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 8, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 5, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 3)), "") + "," + RIGHT(INT(ABS([ROM Value]@row)), 3), IF(LEN(INT(ABS([ROM Value]@row))) <= 12, IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 11, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 9)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 8, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 5, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 3)), "") + "," + RIGHT(INT(ABS([ROM Value]@row)), 3)))))

Paul Newcome

Take the below and drop it into your existing formula in place of [ROM Value]@row.

=IF(……………, " ROM: $" + insert the below here + " ("…………..)

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

The above will take care of any number less than 1 trillion.

.

Sandy Glassberg

OMG thank you so much!!! That worked perfectly!

Slight adjustment, if possible - I don't actually need the cents ".00" that this formula generates. I tried editing on my own to remove those decimal points, but that didn't quite work. Are you able to help me out?

Again, thank you!

Paul Newcome

This should take care of it:

IF(LEN(INT(ABS([ROM Value]@row))) <= 3, RIGHT(INT(ABS([ROM Value]@row)), 3), IF(LEN(INT(ABS([ROM Value]@row))) <= 6, IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 5, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 3)), "") + "," + RIGHT(INT(ABS([ROM Value]@row)), 3), IF(LEN(INT(ABS([ROM Value]@row))) <= 9, IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 8, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 5, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 3)), "") + "," + RIGHT(INT(ABS([ROM Value]@row)), 3), IF(LEN(INT(ABS([ROM Value]@row))) <= 12, IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 11, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 9)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 8, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 5, 3), LEFT(INT(ABS([ROM Value]@row)), LEN(INT(ABS([ROM Value]@row))) - 3)), "") + "," + RIGHT(INT(ABS([ROM Value]@row)), 3)))))

Sandy Glassberg

Again and again - thank you!!! That did the trick!

Paul Newcome

Happy to help. 👍️