Number with Commas in Formula (8,000 instead of 8000)

Anthony Barthelemy

Smartsheet Community:

My Formula is Correct, but I am unable to include Commas to the Numbers when the Results are Displayed.

Here is is My Current Formula:

SUM(CHILDREN()) + " Fiber Used Out Of " + (SUM([Fiber Used (Feet)]2, " ") + " Feet")

Result:

8604 Fiber Used Out Of 12000 Feet

I would Like Commas for the Number (i.e.8,604 and 12,000

Thanks

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @Anthony Barthelemy

Hope you are fine, you need to add a helper column for the numbers to take the Thousands comma format then you can create the text.

Cleversheet

@Anthony Barthelemy,

I was able to achieve the following

Screenshot_2022-02-12 14.38.17_wZFIcw.png

using this formula in a helper (in my case, I put the helper in a SummaryField)

=IF(Processed@row < 0, "-", "") + IFERROR(MID(Processed@row, IF(Processed@row < 0, 2, 1), IF(Processed@row < 0, MOD(FIND(".", Processed@row), 3) + 1, MOD(FIND(".", Processed@row) - 2, 3) + 1)), "") + IF(OR(AND(Processed@row >= 0, LEN(Processed@row) > 9, LEN(Processed@row) < 13), AND(Processed@row < 0, LEN(Processed@row) > 10, LEN(Processed@row) < 14)), "," + IFERROR(MID(Processed@row, FIND(".", Processed@row) - 3, 3), "")) + IF(OR(AND(Processed@row >= 0, LEN(Processed@row) > 12, LEN(Processed@row) < 16), AND(Processed@row < 0, LEN(Processed@row) > 13, LEN(Processed@row) < 17)), "," + IFERROR(MID(Processed@row, FIND(".", Processed@row) - 6, 3) + "," + MID(Processed@row, FIND(".", Processed@row) - 3, 3), ""))

You can learn more about this formula at this posting:

https://community.smartsheet.com/discussion/60671/convert-numbers-to-text-so-i-can-show-a-range-keeping-the-comma-delimiters

Important: the formula relies on the starting number being processed as a decimal number, so I had to add a very small fraction (0.00001) to each source number in order to make it work—but unless your use case is quite sophisticated and depends on super-high precision that should not be a problem.

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an (Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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.