Strip or remove non-numeric characters from text strings

Sometimes, you may need to remove all of the non-numeric characters from the text strings, and only keep the numbers as below screenshot shown. This article will introduce some formulas for solving this task in Excel.

• Strip or remove all non-numeric characters from text strings with formulas
• Strip or remove all non-numeric characters from text strings with an easy feature

Strip or remove all non-numeric characters from text strings with formulas

In Excel 2019 and Office 365, a new TEXTJOIN function combining with the IFERROR, MID, ROW and INDIRECT functions can help you to extract only numbers from a text string, the generic syntax is:

• text:the text string or cell value that you want to remove all non-numeric characters from.

1. Please copy or enter the below formula into a blank cell where you want to output the result:

2. And then, press Ctrl + Shift + Enter keys together to get the first result, see screenshot:

3. Then, select the formula cell, and then drag the fill handle down to the cells that you want to apply this formula, only numbers have been extracted, and all other non-numeric characters have been stripped, see screenshot:

Explanation of the formula:

ROW(INDIRECT("1:100")：Number 1:100 in the INDIRECT formula means the MID function evaluates 100 characters of the text string. This array will contain 100 numbers like this: {1;2;3;4;5;6;7;8....98;99;100}.
Note: If your text string is much longer, you can change the number 100 to larger number as you need.

MID(A2,ROW(INDIRECT("1:100")),1: This MID function is used to extract text in cell A2 to get one character, and it will an array like this:
{"5";"0";"0";" ";"K";"u";"t";"o";"o";"l";"s";" ";"f";"o";"r";" ";"E";"x";"c";"e";"l";"";"";"";"";"";""...}

MID(A2,ROW(INDIRECT("1:100")),1)+0: Adding 0 value after this array is used to force the text to a number, the numeric text value will be converted to number, and non-numeric values will be displayed as #VALUE error value like this:
{"5";"0";"0";#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE! !;#VALUE! !;#VALUE!...}

IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0: This IFERROR function is used to replace all of the error values with an empty string like this:
{"5"; "0";"0";"";""; "";"";"";"";"";""; … }

TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,"")): At last, this TEXTJION function will combine all non-empty values in the array which returned by the IFFERROR function and returns the result.

Notes:

1. With the above formula, the numbers will be returned as text format, if you need a real numeric value, please apply this formula, remember to press Ctrl + Shift + Enter keys together to get the correct result.

2. In early versions Excel, this formula will not work, in this case, the following formula may help you, please copy or enter this formula into a blank cell:

Strip or remove all non-numeric characters from text strings with an easy feature

Relative functions used:

More articles: