Exceljet

Quick, clean, and to the point

Strip non-numeric characters

Excel formula: Strip non-numeric characters
Generic formula 
{=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:100")),1)+0,""))}
Summary 

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

{=TEXTJOIN("",TRUE,IFERROR(MID(B5,ROW(INDIRECT("1:100")),1)+0,""))}

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

Explanation 

In this example, the goal is to remove non-numeric characters from a text string with a formula.

Working from the inside out, the MID function is used to extract the text in B5, one character at a time. The key to this step is the use of the ROW function with the INDIRECT function here:

ROW(INDIRECT("1:100"))

This snippet 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; adjust to suit your data.

This array goes into the MID function as the start_num argument. For num_chars, we use 1. With these inputs, the MID function returns an array like this:

{"1";"0";"0";" ";"a";"p";"p";"l";"e";"s";"";"";"";""...}

Note: extra items in the array removed for readability.

To this array, we add zero. This is a simple trick that forces Excel to try and coerce a value to a number. Numeric text values like "1","2","3","4" etc. are converted, while non-numeric values fail and throw a #VALUE error. We use the IFERROR function to catch these errors and return an empty string (""), while numeric values pass through into an output array. The result is an array that contains just numbers and empty strings:

{1;0;0;"";"";"";"";"";....}

Finally, this array result is delivered to the TEXTJOIN function as the text1 argument. For delimiter, we use an empty string ("") and for ignore_empty we supply TRUE, since we only want to work with the actual values that remain. TEXTJOIN then concatenates all non-empty values in the array and returns a final result.

Note: TEXTJOIN will return the numbers as text, for example "100,"500", etc. If you want a true numeric result, you can add zero (+0), or wrap the entire formula in the VALUE function.

Strip numeric characters

To strip numeric characters, and preserve only text characters, you can use a formula like this:

{=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),""))}

This formula is explained here.

Author 
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.