How to replace string with the number

user_11687602 0
Viewed 667

I want to replace the comma separated letters in a cell with the numbers. I've used the=LOOKUP(A1,{"a","b","c","d","e"},{1,2,3,4,5}) function but it's only working for the single letter.

for example: my desired output: 

a => 1
excel-formula excel
1 Answers

If you have Excel 2016+ with the TEXTJOIN function, you can use the array formula:

=TEXTJOIN(",",TRUE,CODE(UPPER(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s")))-64)

Since this is an array formula, you need to "confirm" it by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar

Algorithm:

  • Convert comma-separated string to XML: "<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>"
  • Use FILTERXML to convert the XML to an array of strings
  • UPPER to make case insensitive
  • CODE(…) - 64 to convert to a series of numbers related to the letter)
  • TEXTJOIN to put the result together

enter image description here

user_28729220
Related