Dollar values keeping the comma delimiters

Bobby1

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

Genevieve P.

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

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

Genevieve P.

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