Convert Numbers to Text so I can show a range keeping the comma delimiters
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 ✭✭✭✭✭✭这是……你比我更复杂ght 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,如果(MOD (LEN((报告高价)2),3)= 0,3,国防部(LEN([Report High Price]2), 3))) + IF(LEN([Report High Price]@row)> 3, "," + MID([Report High Price]@row,如果(MOD (LEN((报告高价)2),3)= 0,3,国防部(LEN([Report High Price]2), 3)) + 1, 3)) + IF(LEN([Report High Price]@row)> 6, "," + MID([Report High Price]@row,如果(MOD (LEN((报告高价)2),3)= 0,3,国防部(LEN([Report High Price]2), 3)) + 4, 3))
-
L_123 ✭✭✭✭✭✭
Sorry missed some@rowmentions. 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), "")
.
此模式后您就会添加程序er 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.)
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. :)
Categories
<\/p>
=VALUE(IFERROR(JOIN(DISTINCT(COLLECT([Customer PO Amount (USD)]@row:[Customer PO Amount (Local Currency)]@row, [Customer PO Amount (USD)]@row:[Customer PO Amount (Local Currency)]@row, <>\"\"))), \" \"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109460,"type":"question","name":"IF \/ OR formula then Check a Box","excerpt":"I need a formula that IF a specific column has either 1 of 2 specific choices, then a BOX in another column is checked: =IF(([Study Status]@row = \"Active\/ Open to Accrual\", 1) OR([Study Status]@row = \"In Start-Up\", 1))","snippet":"I need a formula that IF a specific column has either 1 of 2 specific choices, then a BOX in another column is checked: =IF(([Study Status]@row = \"Active\/ Open to Accrual\", 1)…","categoryID":321,"dateInserted":"2023-08-25T13:30:39+00:00","dateUpdated":null,"dateLastComment":"2023-08-25T16:21:27+00:00","insertUserID":9250,"insertUser":{"userID":9250,"name":"Susan Swisher","url":"https:\/\/community.smartsheet.com\/profile\/Susan%20Swisher","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-25T16:23:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":45,"score":null,"hot":3385956126,"url":"https:\/\/community.smartsheet.com\/discussion\/109460\/if-or-formula-then-check-a-box","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109460\/if-or-formula-then-check-a-box","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109460,"commentID":392616,"name":"Re: IF \/ OR formula then Check a Box","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392616#Comment_392616","dateInserted":"2023-08-25T16:21:27+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T15:13:03+00:00","dateAnswered":"2023-08-25T14:14:39+00:00","acceptedAnswers":[{"commentID":392575,"body":"
Give this a try:<\/p>
=IF(OR([Study Status]@row = \"Active\/Open to Accrual\", [Study Status]@row = \"In Start-Up\"), 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109457,"type":"question","name":"Conditional Formatting (modified date not in the last 3 weeks)","excerpt":"I would like to use Conditional Formatting (highlight the entire row yellow) if the modified date is not within the last 3 weeks. So any row that has not been recently updated (last 3 weeks) should be highlighted in yellow. Is this spmething that can be done directly within conditional formatting or should I first create a…","snippet":"I would like to use Conditional Formatting (highlight the entire row yellow) if the modified date is not within the last 3 weeks. So any row that has not been recently updated…","categoryID":321,"dateInserted":"2023-08-25T12:33:14+00:00","dateUpdated":null,"dateLastComment":"2023-08-25T12:40:57+00:00","insertUserID":161267,"insertUser":{"userID":161267,"name":"Jef Snyders","title":"Jef Snyders","url":"https:\/\/community.smartsheet.com\/profile\/Jef%20Snyders","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4HJAEW33KBD0\/nXEKEZE5EQEV4.jpg","dateLastActive":"2023-08-25T13:32:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161267,"lastUser":{"userID":161267,"name":"Jef Snyders","title":"Jef Snyders","url":"https:\/\/community.smartsheet.com\/profile\/Jef%20Snyders","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4HJAEW33KBD0\/nXEKEZE5EQEV4.jpg","dateLastActive":"2023-08-25T13:32:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3385935251,"url":"https:\/\/community.smartsheet.com\/discussion\/109457\/conditional-formatting-modified-date-not-in-the-last-3-weeks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109457\/conditional-formatting-modified-date-not-in-the-last-3-weeks","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":109457,"commentID":392553,"name":"Re: Conditional Formatting (modified date not in the last 3 weeks)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392553#Comment_392553","dateInserted":"2023-08-25T12:40:57+00:00","insertUserID":161267,"insertUser":{"userID":161267,"name":"Jef Snyders","title":"Jef Snyders","url":"https:\/\/community.smartsheet.com\/profile\/Jef%20Snyders","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4HJAEW33KBD0\/nXEKEZE5EQEV4.jpg","dateLastActive":"2023-08-25T13:32:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T12:40:09+00:00","dateAnswered":"2023-08-25T12:37:47+00:00","acceptedAnswers":[{"commentID":392551,"body":"
There is no direct way to do this. Adding a checkbox helper column with something similar to this and then formatting based on the checkbox is the simplest way. You can even hide the extra column, as there is no reason for it to be visible.<\/p>
=IF([Modified Date]@row < TODAY(-21), 1, 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":4991,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
