Converting scale descriptors to numbers

[mason.robj]

Hi all,

Sorry in advance if this is in the wrong place.

I have some data from a 5-point Likert scale that unfortunately has been exported as text - and I would like to convert it to numbers.

Column A has the descriptive text in it, and it'll be one of 5 options: very fresh, fresh, normal, tired, very tired. I would like to be able to convert the text to a number in column B. Very fresh = 5, fresh = 4, normal = 3, and so on.

For example, A1 has the text "normal" in it. I would like a formula for cell B1 that can recognise the text in A1 and shows the corresponding number, so that B1 ends up reading "3".

Thanks in advance for your help with my rather novice question!

[JohnTopley]

Try

A table as below in X2:Y6 (or wherever)

Very fresh 1
Fresh 2
Normal 3
Tired 4
Very tired 5

in B1 ...

=VLOOKUP($A1,$X$2:$Y$6,2,0)

will return number corresponding to text in A1

BUT the Likert strings are "embedded" in a test string .." this is a fresh idea" ???

[avk]

in "B1"

Please Login or Register  to view this content.

[avk]

in "B1"

Please Login or Register  to view this content.

[Tony Valko]

One way...

=IFERROR(MATCH(A1,{"Very Tired","Tired","Normal","Fresh","Very Fresh"},0),"")

[mason.robj]

Thanks so much everyone - solved!