Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365. 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: 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: 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: 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: 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:
With SEQUENCE
In Excel 365, the new SEQUENCE function can replace the ROW + INDIRECT code above: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
