Explanation
Excel doesn’t have a way to cast the letters in a text string to an array directly in a formula. As a workaround, this formula uses the MID function, with help from the ROW and INDIRECT functions to achieve the same result. The formula in C5, copied down, is:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
This looks pretty complicated but the gist is that we create an array of all characters in B5, and test each character to see if it’s a number. If so, we discard the value and replace it with an empty string (""). If not, we add the non-numeric character to a “processed” array. Finally, we use the TEXTJOIN function (new in Excel 2019) to concatenate all characters together, ignoring empty values.
Working from the inside out, the MID function is used to extract the text in B5, one character at a time. The key is the ROW and INDIRECT snippet here:
ROW(INDIRECT("1:100"))
which spins up an array containing 100 numbers like this:
{1,2,3,4,5,6,7,8….99,100}
Note: 100 represents the maximum characters to process. Change to suit your data, or use the LEN function as explained below.
This array goes into the MID function as the start_num argument. For num_chars , we use 1.
The MID function returns an array like this:
{"3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";...}
Note: extra items in the array removed for readability.
To this array, we add zero. This is a simple trick that forces Excel to coerce text to a number. Numeric text values like “1”,“2”,“3”,“4” etc. are converted without errors, but non-numeric values will fail and throw a #VALUE error. We use the IF function with the ISERR function to catch these errors. When we see an error, we know we have a non-numeric character, so we bring that character into the processed array with another MID function:
MID(B5,ROW(INDIRECT("1:100")),1)
If don’t get an error, we know we have a number, so we insert an empty string ("") into the array in place of the number.
The final array result goes into the TEXTJOIN function as the text1 argument. For delimiter , we use an empty string ("") and for ignore_empty we supply TRUE. TEXTJOIN then concatenates all non-empty values in the array and returns the result.
Precise array length
Instead of hardcoding a number like 100 into INDIRECT, you can use the LEN function to build an array with the actual number of characters in the cell like this:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
LEN returns the count of characters in the cell as a number, which is used instead of 100. This allows the formula to scale up to any number of characters automatically.
Removing extra space
When you strip numeric characters, you may have extra space characters left over. To strip leading and trailing spaces, and normalize spaces between words, you can wrap the formula shown on this page inside the TRIM function :
=TRIM(formula)
With SEQUENCE
In Excel 365 , the new SEQUENCE function can replace the ROW + INDIRECT code above:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Here, we use SEQUENCE + LEN to build an array of the correct length in one step.
With LET
We can further streamline this formula with the LET function . Because the array is created twice above with SEQUENCE and LEN, we can define array as a variable, and create it just once:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Here value of array is set just once, then used twice inside the MID function.
Strip non-numeric characters
You can use a similar formula to remove non-numeric characters .
Explanation
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.
To parse the input string into an array or letters, we use MID, ROW, LEN and INDIRECT functions like this:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN returns the length of the input text, which is concatenated to “1:” and handed off to INDIRECT as text. INDIRECT evaluates the text as a row reference, and the ROW function returns an array of numbers to MID:
MID(B5,{1;2;3},1)
MID then extracts one character for at each starting position and we have:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,{"a";"b";"c"})),xtable,2,0))
Essentially, we are asking VLOOKUP to find a match for “a”, “b”, and “c” at the same time. For obscure reasons , we need to “dereference” this array in a particular way using both the T and IF functions. After VLOOKUP runs, we have:
=TEXTJOIN("",1,{9;4;6})
and TEXTJOIN returns the string “946”.
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.
Sum numbers
To sum the numbers together instead of listing them, you can replace TEXTJOIN with SUM like this:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Note: the TEXTJOIN function was introduced via the Office 365 subscription program in 2018.
