Return cells containing numbers as text string

Philipp Zünd

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.

Bassam Khalil

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.

Philipp Zünd

@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".

Bassam Khalil

Hi @Philipp Zünd 

Sorry but you can't do that in smartsheet now as i know.

Andrée Starå

Hi @Philipp Zünd

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!

Philipp Zünd

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?

Andrée Starå

@Philipp Zünd

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)

Erin Ballantine

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.

Paul H

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

Messsk

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.