Dollar values keeping the comma delimiters

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

Find more posts tagged with

Finance

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.