Summary
To translate letters in a text string to numbers, you can use a formula based on the XLOOKUP, MID, SEQUENCE, and CONCAT functions, with a translation table to provide the lookups. In the example shown, the formula in D5, copied down, is:
=CONCAT(XLOOKUP(MID(B5,SEQUENCE(LEN(B5)),1),letters,numbers))
where letters (F5:F10) and numbers (G5:G10) are named ranges. The result is a text string with one number for each letter in B5, so "abc" becomes "946". XLOOKUP and SEQUENCE are available in Excel 2021+ and Excel 365.
Generic formula
=CONCAT(XLOOKUP(MID(A1,SEQUENCE(LEN(A1)),1),letters,numbers))
Explanation
In this example, the goal is to translate the letters in each text string in column B to numbers, using the translation table in F5:G10. Each letter should be replaced by its number, and the numbers joined together in a single text string, so "abc" in B5 becomes "946" in D5.
At the core, this formula uses an array operation to generate an array of letters from the input text, translates each letter individually to a number, then joins all numbers together again and returns the output as a string.
Split string to array
To parse the input string into an array of letters, we use the MID, SEQUENCE, and LEN functions like this:
MID(B5,SEQUENCE(LEN(B5)),1)
LEN returns the length of the input text, 3 for "abc", and SEQUENCE uses that count to return an array of numbers to MID:
MID(B5,{1;2;3},1)
MID then extracts one character at each starting position, and returns an array of letters:
{"a";"b";"c"}
Translate letters with XLOOKUP
The array of letters is delivered to XLOOKUP as the lookup value:
XLOOKUP({"a";"b";"c"},letters,numbers)
Because the lookup value is an array of 3 letters, XLOOKUP looks up all 3 at the same time. It finds each letter in letters and returns the matching value from numbers:
=CONCAT({9;4;6})
and CONCAT joins the numbers into the string "946".
Add a delimiter
To put a delimiter between the numbers, replace CONCAT with the TEXTJOIN function, which takes the delimiter as its first argument:
=TEXTJOIN("-",TRUE,XLOOKUP(MID(B5,SEQUENCE(LEN(B5)),1),letters,numbers))
With "abc" in B5, this formula returns "9-4-6". For more on the difference between the two functions, see CONCAT vs TEXTJOIN.
Output a number
To output a number as final result (instead of a string), add zero. The math operation will coerce the string into a number:
=CONCAT(XLOOKUP(MID(B5,SEQUENCE(LEN(B5)),1),letters,numbers))+0
Sum numbers
To sum the numbers together instead of listing them, you can replace CONCAT with SUM like this:
=SUM(XLOOKUP(MID(B5,SEQUENCE(LEN(B5)),1),letters,numbers))
With "abc" in B5, the result is 19 (9+4+6).
Unknown characters and empty cells
XLOOKUP isn't case-sensitive, so "ABC" also returns "946". A character that isn't in the table, including a space, makes XLOOKUP return #N/A, and the whole formula returns #N/A. To skip unknown characters instead, give XLOOKUP an empty string ("") for its if_not_found argument:
=CONCAT(XLOOKUP(MID(B5,SEQUENCE(LEN(B5)),1),letters,numbers,""))
With this version, "abd" returns "94". If B5 is empty, LEN returns zero, and SEQUENCE returns a #CALC! error. To return an empty string for an empty cell, wrap the formula in IF:
=IF(B5="","",CONCAT(XLOOKUP(MID(B5,SEQUENCE(LEN(B5)),1),letters,numbers)))
