Replace IF text with numbers automatically

Options

Hello,


I have a survey coming in to a tracker. I would like to automatically replace the text answers to numerical values in my tracker.

I'd like the cell to look at its text content and replace it with specific numbers:

So if for the "communication accuracy" someone has answered "D'accord/ agree" , smartsheet looks into that cell and says ok, I need to replace this with 4.

If it is "Ni en accord ni en..." then it should replace it with 3.

Is it possible?


thank you!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Abnoos Moslehi

    The formula will go in a new column, not the column where the text resides. You cannot overwrite/replace the input text cell. I'll call this new column [Returned Numerical Value]. You can name it whatever you like

    There's a couple of ways to find the numerical value.

    Option 1: If you have set up a reference (ie a lookup table) just like you presented in your screenshot you can use an index match. My personal preference would be to set up a separate sheet just like your screen shot. Because this would be a cross sheet reference, you cannot just copy-paste this formula in. You must create the reference.

    =INDEX({your reference sheet Number column}, MATCH([your text response]@row, {your reference sheet Text reference},0))

    This formula above says get the term you need (eg. your [Numerical Value]) when a response (your text field) matches your reference table text.

    Option 2: If you don't want to set up some columns or a different sheet to contain the reference information, we can use a nested IF in your [Returned Numerical Value] column. If you get more than these 5 responses, I would strongly recommend building a reference table as the Index/match is cleaner to write and significantly easier to update.

    =IF([your text column with the communication answer]@row<>"",IF([your text column with the communication answer]@row="your response #1", 1, IF([your text column with the communication answer]@row= "your response #2", 2, IF([your text column with the communication answer]@row= "your response #3", 3, IF([your text column with the communication answer]@row= "your response #4", 4, 5)))))

    Replace your [text column] name in the above formula, as well as inserting the phrases (exact spelling and it is case-sensitive).

    cheers

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Abnoos Moslehi

    The formula will go in a new column, not the column where the text resides. You cannot overwrite/replace the input text cell. I'll call this new column [Returned Numerical Value]. You can name it whatever you like

    There's a couple of ways to find the numerical value.

    Option 1: If you have set up a reference (ie a lookup table) just like you presented in your screenshot you can use an index match. My personal preference would be to set up a separate sheet just like your screen shot. Because this would be a cross sheet reference, you cannot just copy-paste this formula in. You must create the reference.

    =INDEX({your reference sheet Number column}, MATCH([your text response]@row, {your reference sheet Text reference},0))

    This formula above says get the term you need (eg. your [Numerical Value]) when a response (your text field) matches your reference table text.

    Option 2: If you don't want to set up some columns or a different sheet to contain the reference information, we can use a nested IF in your [Returned Numerical Value] column. If you get more than these 5 responses, I would strongly recommend building a reference table as the Index/match is cleaner to write and significantly easier to update.

    =IF([your text column with the communication answer]@row<>"",IF([your text column with the communication answer]@row="your response #1", 1, IF([your text column with the communication answer]@row= "your response #2", 2, IF([your text column with the communication answer]@row= "your response #3", 3, IF([your text column with the communication answer]@row= "your response #4", 4, 5)))))

    Replace your [text column] name in the above formula, as well as inserting the phrases (exact spelling and it is case-sensitive).

    cheers

  • Abnoos Moslehi
    Abnoos Moslehi ✭✭✭✭
    Options

    Thank you @KDM

    You made my Monday!😎

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Very happy to help. Thanks for contributing your question to the community.