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

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

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"