Formula Question: How To Convert Phrase to Number

[Benn_Bennett]

Hello!

Does anyone know how to convert a particular phrase to a numerical value (for analysis)? I have a survey with various responses like:
“0% to 25% of the time”
“26%-50% of the time” . . .

I want to convert those to:
“0% to 25% of the time” = 1
“26%-50% of the time” = 2
etc.

I was hoping to have one column where I copy/paste the survey responses, and another formula column that auto-generates the numerical values.

If that doesn’t make sense, I made a test doc here: Test Docs

Thanks for your help!

[Piet_Strydom]

HI Benn

I have provided an example in your doc.

But why convert to a number in the first place?

Regards
Piet

[Thomas_Baucom]

Hi @Benn_Bennett,

I added an example to the doc as well but I think @Piet_Strydom has the more simple solution.

My solution was just using the substitute formula for something like:

thisRow.[Question #1 Answer (text)].Substitute(“None or a little (0-25%) of the day”,“1”).Substitute(“Some (26-50%) of the day”, “2”).substitute(“Most (51-75%) of the day”,“3” ).substitute(“Nearly all (75-100%) of the day”,“4”)

[Scott_Collier-Weir]

Yeah - your options would be:

• Use a helper table like @Piet_Strydom did and then filter off that table to find the appropriate row
• Use a SwitchIf() formula

Let us know if you are looking for a different result!

[Benn_Bennett]

Yes, thank you! I definitely figured it was some external table route, but had no idea where to start with the formulas. Much appreciated!

[Benn_Bennett]

Great solution! Thank you!

[Thomas_Baucom]

I stole that one from @Eric_Koleda!