Return cells containing numbers as text string

Options

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.


Tags:

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    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.

    PMP Certified

    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"

  • Philipp Zünd
    Options

    @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
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/07/20
    Options

    Hi @Philipp Zünd 

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

    PMP Certified

    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"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    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.

  • Philipp Zünd
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

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

    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.

  • Erin Ballantine
    Options

    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
    Paul H ✭✭✭✭✭✭
    Options

    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
    Messsk ✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!