Strip or remove non-numeric characters from text strings (2024)

Author: Xiaoyang Last Modified: 2020-11-06

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:

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

• 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:

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

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.

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

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:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)

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

Relative functions used:

More articles: