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

Buz S.

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).

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

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

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), "")

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

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.

L_123

Whoops. Completely forgot about that.

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

 

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.

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(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), ""))

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.)

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

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 "Processed@row" 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(Processed@row < 0, "-", "") + | Places hypen in front of numbers that should be negative

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 number is three digits or less, or for leftmost of multi-segment numbers, displays the applicable digits

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 number is 4-6 digits, adds the 3-digit hundreds segment to the first segment

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), "")) | If number is 7-9 digits, adds the 3-digit thousands and hundreds segments to the first segment

Bobby1

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

Cleversheet

Hmm, in my use case I didn't need the cents value; the whole dollar value (unrounded*) met my immediate need. I think that addressing the segment portion would require another layer to be added to the formula. Wishing you well building on what Buz, Paul and I have generated thus far. If you come up with a solution that works in all cases I hope you'll post it here for all to take advantage of.

* - I assume the rightmost-three-digits portion of the formula would need to have ROUND() wrapped correctly to get that piece to read with that next level of precision—if you can call a rounded number precise. :)