Quick, clean, and to the point

Strip numeric characters from cell

Excel formula: Strip numeric characters from cell
Generic formula 

To remove numeric characters from a text string, you can use a formula based on the TEXTJOIN function. In the example shown, the formula in C5 is:


Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365.


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:


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:


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:


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:



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
See also 

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.