Formula Question: How To Convert Phrase to Number

#1

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!

#2

HI Benn

I have provided an example in your doc.

But why convert to a number in the first place?

Regards
Piet

#3

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

#4

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!