Forum Discussion
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 agree" -> "4"). I would need to be able to do this across multiple cells with multiple different values that I don't want to change. I have tried the substitute and replace functions and have run into problems with both that don't allow me to change the one specific text value to a specific number across multiple cells with different values. I am hoping there is a way to do this in excel using functions that I just haven't found yet as this is a process that I currently do manually across hundreds of cells.
Would appreciate any help or advice to streamline this process, thanks!
3 Replies
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.
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
In the second sheet in the attached file is a formula that replaces strings with numbers.
Yes you can/should look at XLOOKUP and create a table for text strings and how they should be converted. You can even use wildcard characters to make it more flexible and robust.
