7

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

Functionality
Industry
Department

Comments

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

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

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

In reply to by [email protected]

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.

In reply to by [email protected]

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.