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!
HI Benn
I have provided an example in your doc.
But why convert to a number in the first place?
Regards
Piet
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”)
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!