Return cells containing numbers as text string
how can I keep the number formatting of a cell in a formula? If I want to combine text and number, it reverts the number format to the blank number without decimals and thousands separator.
Answers
-
Hi @Philipp Zünd
Hope you are fine, i think you can't keep the currency format for your number when you join text with number in same cell.
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"
-
@Bassam.M Khalil thanks - but obviously I'm interested to get a solution instead.
the question is if I can somehow convert numbers including their format (with separators and decimals) into text so the sentence says "list price per pc. € 4,210.00" instead of "list price per pc. € 4210".
-
Hi @Philipp Zünd
Sorry but you can't do that in smartsheet now as i know.
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"
-
Try adding +"" at the end of the formula.
Did that work?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you @Andrée Starå - I thought I will never get an answer for my problem. But unfortunately it still doesn't work. I guess you are trying to "fool" the system thinking that it's a text string instead of a number?
-
Strange!
You're more than welcome!
I'd be happy to take a quick look.
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Philip,
I have this (complicated and long) formula:
= "$" + IF(IF(FIND(".", [Number Column]@row) = 0, LEN([Number Column]@row), LEN([Number Column]@row) - (LEN([Number Column]@row) - (FIND(".", [Number Column]@row) - 1))) > 6, IF(FIND(".", [Number Column]@row) = 0, LEFT([Number Column]@row, LEN([Number Column]@row) - 6), LEFT([Number Column]@row, LEN([Number Column]@row) - 6 - IF(FIND(".", [Number Column]@row) = 0, 0, LEN([Number Column]@row) - FIND(".", [Number Column]@row)) - 1)) + ",", "") + IF(IF(FIND(".", [Number Column]@row) = 0, LEN([Number Column]@row), LEN([Number Column]@row) - (LEN([Number Column]@row) - (FIND(".", [Number Column]@row) - 1))) > 3, IF(FIND(".", [Number Column]@row) = 0, RIGHT(LEFT([Number Column]@row, LEN([Number Column]@row) - 3), 3), RIGHT(LEFT([Number Column]@row, LEN([Number Column]@row) - 3 - IF(FIND(".", [Number Column]@row) = 0, 0, LEN([Number Column]@row) - FIND(".", [Number Column]@row)) - 1), 3)) + ",", "") + IF(IF(FIND(".", [Number Column]@row) = 0, LEN([Number Column]@row), LEN([Number Column]@row) - (LEN([Number Column]@row) - (FIND(".", [Number Column]@row) - 1))) > 0, IF(FIND(".", [Number Column]@row) = 0, RIGHT(LEFT([Number Column]@row, LEN([Number Column]@row)), 3), RIGHT(LEFT([Number Column]@row, LEN([Number Column]@row) - IF(FIND(".", [Number Column]@row) = 0, 0, LEN([Number Column]@row) - FIND(".", [Number Column]@row)) - 1), 3)), "") + "." + IF(LEN(RIGHT([Number Column]@row, IF(FIND(".", [Number Column]@row) = 0, 0, LEN([Number Column]@row) - FIND(".", [Number Column]@row)))) = 0, "00", IF(LEN(RIGHT([Number Column]@row, IF(FIND(".", [Number Column]@row) = 0, 0, LEN([Number Column]@row) - FIND(".", [Number Column]@row)))) = 1, RIGHT([Number Column]@row, IF(FIND(".", [Number Column]@row) = 0, 0, LEN([Number Column]@row) - FIND(".", [Number Column]@row))) + "0", (RIGHT(ROUND([Number Column]@row, 2), 2))))
There might be a way to simplify it that I haven't had the time to look at. I think you would have to add to it if you are working with numbers of more than 999,999.99, but for my purposes it was sufficient.
-
Not sure if this is any cleaner but I just did it for the challenge
For the list price I cheated and added a third decimal to force it to pull the decimals then format it so show only two
Step one pull the text
Step two add the first thousands separator
Step three add the second separator
Nested
="$" + IF(LEN(IF(LEN(LEFT([List Price]@row, LEN([List Price]@row) - 1)) > 6, LEFT(LEFT([List Price]@row, LEN([List Price]@row) - 1), LEN(LEFT([List Price]@row, LEN([List Price]@row) - 1)) - 6) + "," + RIGHT(LEFT([List Price]@row, LEN([List Price]@row) - 1), 6))) > 10, LEFT(IF(LEN(LEFT([List Price]@row, LEN([List Price]@row) - 1)) > 6, LEFT(LEFT([List Price]@row, LEN([List Price]@row) - 1), LEN(LEFT([List Price]@row, LEN([List Price]@row) - 1)) - 6) + "," + RIGHT(LEFT([List Price]@row, LEN([List Price]@row) - 1), 6)), LEN(IF(LEN(LEFT([List Price]@row, LEN([List Price]@row) - 1)) > 6, LEFT(LEFT([List Price]@row, LEN([List Price]@row) - 1), LEN(LEFT([List Price]@row, LEN([List Price]@row) - 1)) - 6) + "," + RIGHT(LEFT([List Price]@row, LEN([List Price]@row) - 1), 6))) - 10) + "," + RIGHT(IF(LEN(LEFT([List Price]@row, LEN([List Price]@row) - 1)) > 6, LEFT(LEFT([List Price]@row, LEN([List Price]@row) - 1), LEN(LEFT([List Price]@row, LEN([List Price]@row) - 1)) - 6) + "," + RIGHT(LEFT([List Price]@row, LEN([List Price]@row) - 1), 6)), 10))
-
Incredible how after years of people coming up with workarounds to this incredibly simple problem, Smartsheet still hasn't just given us the option to maintain number formatting when referencing or concatenating cells.
Bananas.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!