Replace text with numeric value

%3CLINGO-SUB%20id%3D%22lingo-sub-2891747%22%20slang%3D%22en-US%22%3EReplace%20text%20with%20numeric%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2891747%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20some%20survey%20results%20that%20come%20out%20as%20text%20that%20I%20need%20to%20transpose%20to%20a%20number.%26nbsp%3B%20For%20example%20Strongly%20like%20would%20get%20a%20value%20of%205%2C%20Like%20-%20value%20of%204%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2891747%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2891786%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20text%20with%20numeric%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2891786%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1197832%22%20target%3D%22_blank%22%3E%40seriousmoonlight%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20to%20instruct%20Excel%20that%20%22Strongly%22%20is%205%2C%20etc.%20That%20could%20be%20a%20separate%20table%20(preferably)%26nbsp%3B%20or%20that%20could%20be%20hardcoded%20in%20formulae.%20Another%20story%20where%20and%20how%20to%20use%20that%2C%20from%20the%20question%20it's%20not%20clear.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have some survey results that come out as text that I need to transpose to a number.  For example Strongly like would get a value of 5, Like - value of 4 etc.

3 Replies

@seriousmoonlight 

You need to instruct Excel that "Strongly" is 5, etc. That could be a separate table (preferably)  or that could be hardcoded in formulae. Another story where and how to use that, from the question it's not clear.

Perfect - I can create a table that shows the two values. I have two columns - one has text in it - would like the numeric value in next column. Does that explain it better

@seriousmoonlight 

You may use something like

=IFERROR( INDEX( numberColumn, MATCH( textToLookup, textColumn, 0 ) ), "no such")