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

Anthony Barthelemy
Anthony Barthelemy ✭✭✭✭✭✭
edited 08/07/21 in Formulas and Functions

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

Answers

  • Bassam Khalil
    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.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Cleversheet
    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:

    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"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!