Forum Discussion

KaylenPFO's avatar
KaylenPFO
Occasional Reader
Feb 11, 2026

Replacing a string of words with a single number

I work with a lot of outcome data from a survey that has words as the typical response to questions. I need to convert those words to a number value so that I can analyze the data (example: "I really...
excel
Formulas and Functions
Olufemi7's avatar
Olufemi7
Iron Contributor
Feb 11, 2026

HelloKaylenPFO​,

You don’t need SUBSTITUTE or REPLACE for this. The correct approach is to use a lookup table to map each text response to its numeric value. Create a small conversion table somewhere in the sheet, for example: I strongly disagree = 1, I disagree = 2, I agree = 3, I really agree = 4. If your survey responses are in A2:A and the mapping table is in H2:I5, use =XLOOKUP(A2,$H$2:$H$5,$I$2:$I$5,"") and copy the formula down. It will return the corresponding number for matched text and leave blanks for values not found in the table. If you're using an older version of Excel, use =VLOOKUP(A2,$H$2:$I$5,2,FALSE). If you want unmatched values to remain unchanged, use =IFERROR(XLOOKUP(A2,$H$2:$H$5,$I$2:$I$5),A2). This approach is scalable, easy to maintain, and avoids the partial text replacement issues that occur with SUBSTITUTE or REPLACE.