Dollar values keeping the comma delimiters

Bobby1
Bobby1 ✭✭✭✭
edited 06/28/22 in Formulas and Functions

I repurposed the formula to this below. Now I have a range from 10's of thousands to 100's millions. how do I not get that final comma next to the period automatically?

="$" + LEFT([Unit price]@row, IF(MOD(LEN([Unit price]@row), 3) = 0, 3, MOD(LEN([Unit price]@row), 3))) + IF(LEN([Unit price]@row) > 3, "," + MID([Unit price]@row, IF(MOD(LEN([Unit price]@row), 3) = 0, 3, MOD(LEN([Unit price]@row), 3)) + 1, 3)) + IF(LEN([Unit price]@row) > 6, "," + MID([Unit price]@row, IF(MOD(LEN([Unit price]@row), 3) = 0, 3, MOD(LEN([Unit price]@row), 3)) + 4, 3))

And I am getting a comma next to the period for cents

$79,016,.25


Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Bobby1

    Can I ask why you're not using the Column Formatting to apply the $ sign and automatically add in the correct commas and periods? How is your [Unit price]@row added into the sheet?

  • Bobby1
    Bobby1 ✭✭✭✭

    @Genevieve P. I hope this screen shot helps to explain a little better

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Bobby1

    Thank you for clarifying that you're looking to bring this value into another cell with text values, so you're looking to add in the commas as text with the number, the screen capture was very helpful!

    The issue here is that the LEN or length of your value is including in the decimals. I would add a ROUND function to each of your LEN statements, then at the end add a statement to bring back in the decimals if the number has them:

    ="$" + LEFT([Unit Price]@row, IF(MOD(LEN([Unit Price]@row), 3) = 0, 3, MOD(LEN([Unit Price]@row), 3))) + IF(LEN(ROUND([Unit Price]@row)) > 3, "," + MID([Unit Price]@row, IF(MOD(LEN([Unit Price]@row), 3) = 0, 3, MOD(LEN([Unit Price]@row), 3)) + 1, 3)) + IF(LEN(ROUND([Unit Price]@row)) > 6, "," + MID([Unit Price]@row, IF(MOD(LEN([Unit Price]@row), 3) = 0, 3, MOD(LEN([Unit Price]@row), 3)) + 4, 3)) + IF(FIND(".", [Unit Price]@row) > 0, RIGHT([Unit Price]@row, 3))


    Let me know if this did the trick!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!