Replace IF text with numbers automatically

Abnoos Moslehi

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!

Kelly Moore

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

Kelly Moore

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

Thank you @KDM

You made my Monday!😎

Kelly Moore

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