Replace text with numeric value

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


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


You may use something like

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