Convert Numbers to Text so I can show a range keeping the comma delimiters

Buz S.
edited 12/09/19 in Smartsheet Basics

I have two columns of data showing prices, say $1,100 in one and $2,200 in another.  I want to display this as a range in a report as "Your price is $1,100 - $2,200".  I used this formula: 

"$" + $[Report Low Price]@row + " - $" + ($[Report High Price]@row

which returns "$1100 - $2200", how can i have it include the comma separator?  The values range between $100 to over $1M (needing two commas per number).

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    This was.... a little more complicated than I thought when I started it. If anyone wants to try to improve on it I am interested in other solutions

    ="$" + LEFT([Report Low Price]@row, IF(MOD(LEN([Report Low Price]@row), 3) = 0, 3, MOD(LEN([Report Low Price]@row), 3))) + IF(LEN([Report Low Price]@row) > 3, "," + MID([Report Low Price]@row, IF(MOD(LEN([Report Low Price]@row), 3) = 0, 3, MOD(LEN([Report Low Price]@row), 3)) + 1, 3)) + IF(LEN([Report Low Price]@row) > 6, "," + MID([Report Low Price]@row, IF(MOD(LEN([Report Low Price]@row), 3) = 0, 3, MOD(LEN([Report Low Price]@row), 3)) + 4, 3)) + " - $" + LEFT([Report High Price]@row, IF(MOD(LEN([Report High Price]2), 3) = 0, 3, MOD(LEN([Report High Price]2), 3))) + IF(LEN([Report High Price]@row) > 3, "," + MID([Report High Price]@row, IF(MOD(LEN([Report High Price]2), 3) = 0, 3, MOD(LEN([Report High Price]2), 3)) + 1, 3)) + IF(LEN([Report High Price]@row) > 6, "," + MID([Report High Price]@row, IF(MOD(LEN([Report High Price]2), 3) = 0, 3, MOD(LEN([Report High Price]2), 3)) + 4, 3))

  • L_123
    L_123 ✭✭✭✭✭✭

    Sorry missed some @row mentions. Hopefully this is copy paste for you.

    ="$" + LEFT([Report Low Price]@row, IF(MOD(LEN([Report Low Price]@row), 3) = 0, 3, MOD(LEN([Report Low Price]@row), 3))) + IF(LEN([Report Low Price]@row) > 3, "," + MID([Report Low Price]@row, IF(MOD(LEN([Report Low Price]@row), 3) = 0, 3, MOD(LEN([Report Low Price]@row), 3)) + 1, 3)) + IF(LEN([Report Low Price]@row) > 6, "," + MID([Report Low Price]@row, IF(MOD(LEN([Report Low Price]@row), 3) = 0, 3, MOD(LEN([Report Low Price]@row), 3)) + 4, 3)) + " - $" + LEFT([Report High Price]@row, IF(MOD(LEN([Report High Price]@row), 3) = 0, 3, MOD(LEN([Report High Price]@row), 3))) + IF(LEN([Report High Price]@row) > 3, "," + MID([Report High Price]@row, IF(MOD(LEN([Report High Price]@row), 3) = 0, 3, MOD(LEN([Report High Price]@row), 3)) + 1, 3)) + IF(LEN([Report High Price]@row) > 6, "," + MID([Report High Price]@row, IF(MOD(LEN([Report High Price]@row), 3) = 0, 3, MOD(LEN([Report High Price]@row), 3)) + 4, 3))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you are using no more than 9 digits and don't mind using helper columns, you can accomplish this with a series of much shorter formulas. It would require 8 helper columns in total. These can be condensed into longer formulas because cell references are used across a row. I personally just find it easier to break it down into sections.

     

    Basically we will use 4 columns per number. 3 to break them down into sections, then the 4th to bring them back together with the commas. I will provide an example here for just one number, you would simply duplicate this solution for the second number.

    .

    I will use this number in the below solution as a visual reference:

    987,654,321

    .

    Columns:

    [First Number]: This is the column housing the first original number. $987,654,321.00

    [First Millions]: If the [First Number] column has 7, 8, or 9 digits, this column will house those digits respectively. 987,654,321

    [First Thousands]: If the [First Number] column has 4, 5, or 6 digits, this column will house those digits respectively. 987,654,321

    [First Hundreds]: If the [First Number] column has 4, 5, or 6 digits, this column will house those digits respectively. 987,654,321

    [First Final]: This will join each of those sections together using a comma as a delimiter. 987,654,321

    .

    [First Number]

    This would be your original dollar amount.

    .

    [First Millions]

    =IFERROR(RIGHT(LEFT([First Number]@row, LEN([First Number]@row) - 6), 3), "")

    .

    [First Thousands]

    =IFERROR(RIGHT(LEFT([First Number]@row, LEN([First Number]@row) - 3), 3), "")

    .

    [First Hundreds]

    =RIGHT([First Number]@row, 3)

    .

    [First Final]

    =JOIN(COLLECT([First Millions]@row:[First Hundreds]@row, [First Millions]@row:[First Hundreds]@row, ISTEXT(@cell)), ",")

    .

    .

    Once you duplicate this for the second number, you can reference the "Final" columns in your original formula.

     

    "$" + $[First Final]@row + " - $" + ($[Second Final]@row

    .

    The nice thing is that this is easily scaleable. If you need to add in Billions, just add another helper column for that section and take the formula from the Millions section and replace the 6 with a 9 then include it in the JOIN(COLLECT(...)).

    =IFERROR(RIGHT(LEFT([First Number]@row, LEN([First Number]@row) - 9), 3), "")

    .

    Following this pattern you would just add in another column for each 3 digit section and increase that number by 3.

    .

    .

    .

    Of course since cell references are used, you can consolidate these smaller formulas into larger ones to avoid using helper columns, but I am really not a fan of massive formulas if they can be avoided.

    Using my method for both numbers and combining them into the string you wanted all in one massive formula would look something like this...

     

    ="$" + IF(ISTEXT(IFERROR(RIGHT(LEFT($[First Number]@row, LEN($[First Number]@row) - 6), 3), "")), IFERROR(RIGHT(LEFT($[First Number]@row, LEN($[First Number]@row) - 6), 3), "") + ",", "") + IF(ISTEXT(IFERROR(RIGHT(LEFT($[First Number]@row, LEN($[First Number]@row) - 3), 3), "")), IFERROR(RIGHT(LEFT($[First Number]@row, LEN($[First Number]@row) - 3), 3), "") + ",", "") + IF(ISTEXT(RIGHT($[First Number]@row, 3)), RIGHT($[First Number]@row, 3), "") + " - $" + IF(ISTEXT(IFERROR(RIGHT(LEFT($[Second Number]@row, LEN($[Second Number]@row) - 6), 3), "")), IFERROR(RIGHT(LEFT($[Second Number]@row, LEN($[Second Number]@row) - 6), 3), "") + ",", "") + IF(ISTEXT(IFERROR(RIGHT(LEFT($[Second Number]@row, LEN($[Second Number]@row) - 3), 3), "")), IFERROR(RIGHT(LEFT($[Second Number]@row, LEN($[Second Number]@row) - 3), 3), "") + ",", "") + IF(ISTEXT(RIGHT($[Second Number]@row, 3)), RIGHT($[Second Number]@row, 3), "")

    thinkspi.com

  • L_123
    L_123 ✭✭✭✭✭✭

    Yeah, mine can be broken down in much the same way. With 5 columns total they turn into reasonable formulas that can be scaled for extra digits easily. I just didn't post the workflow in my response, maybe I should have.

    https://app.smartsheet.com/b/publish?EQBCT=d5e5803d757c4a8786a060b4981ab5f4

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I figured it could be broken down, but it is definitely not the most obvious breakdown just looking at the overall formula. Haha.

     

    P.S.

    SS recently made an update where the default publish is to "users in the owner's account". You now have to make it a point to change it each time you publish to "allow anyone with the link" to view.

    thinkspi.com

  • L_123
    L_123 ✭✭✭✭✭✭
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Haha. No worries. I can see it now. 

     

    Basically it looks like the main difference between your formulas an mine is that I am working right to left and you are working left to right. Both of which are done in a similar (and similarly complex) manner to arrive at the same solution.

    .

    That means my solution didn't achieve the goal of being more simple. Haha.

    thinkspi.com

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭

    I found that both Buz's and Paul's versions worked flawlessly for positive whole numbers, but unfortunately both failed when processing decimals, and certain negative numbers. I devised an alternate approach that appears to properly display whole numbers in text with commas in all cases (thru millions, with unlimited decimal places; would need to be adapted for billions segment).

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

    Here is a public sheet showing the formula in use, with columns to show the development thought process; that is, the elements of the formula initially isolated, then combined to yield the working version. (Buz's and Paul's formulas, and their pain points, are displayed in the rightmost columns.)

    Tip: Perhaps obvious to most, but some may be helped to know you can copy the above formula into a cell in Smartsheet, select the cell, use Cmd-f (Ctl-f in Windows) to open the Find dialog, place  in the left field "Processed" (without quotes) and your own [ColumnName] designator (or "[email protected]" and your own SummaryField#) in the right field, then tap ReplaceAll to make it easy to convert this formula for your own use.

    Here's a formula walk-thru:

    =IF([email protected] < 0, "-", "") + | Places hypen in front of numbers that should be negative

    IFERROR(MID([email protected], IF([email protected] < 0, 2, 1), IF([email protected] < 0, MOD(FIND(".", [email protected]), 3) + 1, MOD(FIND(".", [email protected]) - 2, 3) + 1)), "") + | If number is three digits or less, or for leftmost of multi-segment numbers, displays the applicable digits

    IF(OR(AND([email protected] >= 0, LEN([email protected]) > 9, LEN([email protected]) < 13), AND([email protected] < 0, LEN([email protected]) > 10, LEN([email protected]) < 14)), "," + IFERROR(MID([email protected], FIND(".", [email protected]) - 3, 3), "")) + | If number is 4-6 digits, adds the 3-digit hundreds segment to the first segment

    IF(OR(AND([email protected] >= 0, LEN([email protected]) > 12, LEN([email protected]) < 16), AND([email protected] < 0, LEN([email protected]) > 13, LEN([email protected]) < 17)), "," + IFERROR(MID([email protected], FIND(".", [email protected]) - 6, 3) + "," + MID([email protected], FIND(".", [email protected]) - 3, 3), "")) | If number is 7-9 digits, adds the 3-digit thousands and hundreds segments to the first segment

  • Hi I wanted to follow up on this thread 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