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!