Go Back

Strip non-numeric characters

Excel allows us to strip non-numeric characters from the string by using several Excel functions: TEXTJOIN, IFERROR, MID, ROW and INDIRECT. This step by step tutorial will assist all levels of Excel users in extracting the numeric characters from the text.

Figure 1. Remove non-numeric characters from the text

Syntax of the TEXTJOIN Formula

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

The parameters of the TEXTJOIN function are:

  • delimiter – a string that will be used as a separator between the text items
  • ignore_empty – TRUE for ignoring empty cells
  • text1, [text2] – text strings that will be joined

Syntax of the IFERROR Formula

=IFERROR(value, value_if_error)

The parameters of the IFERROR function are:

  • value – a cell that is evaluated
  • value_if_error – value to be inserted if an error exists in a cell

Syntax of the MID Formula

=MID(text, start_num, num_chars)

The parameters of the MID function are:

  • text – a string from which we want to extract the characters
  • start_num – position of the first character that we want to extract from the string
  • num_chars – a number of characters that we want to extract from the first character

ROW and INDIRECT are helper functions in the MID formula array syntax and explanation for this formula part will be provided through the example.

Setting up Our Data for Stripping Non-numeric Characters

Our table consists of 2 columns: “Text” (column B) and “Number from Text” (column C). The idea is to strip non-numeric characters from the string and to place the numeric characters in column C.

Figure 2. Table structure with cells containing text and numbers

Get numeric characters from the string

We want to extract only numeric characters from the string in column “Text”. This can be done with the combination of several functions: TEXTJOIN, IFERROR, MID, ROW and INDIRECT.

The formula looks like:

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

A formula has to be an array function in order to work properly. To create an array function we have to click simultaneously ctr, shift, and enter on the formula.

To apply the formula we need to follow these steps:

  • Select cell C3 and click on it
  • Insert formula: =TEXTJOIN("",TRUE,IFERROR(MID(B3,ROW(INDIRECT("1:30")),1)+0,""))
  • Press ctrl, shift and enter simultaneously to create an array
  • Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

Figure 3. A formula for stripping non-numeric characters from the string

MID formula extracts each character of the string with the help of the formula part: ROW(INDIRECT(“1:30”)). Number ”1:30” in the INDIRECT formula means that MID function evaluates 30 characters of the string. We can enter a higher number if a string contains more characters.

MID function returns an array of 30 characters in a string:

{“M”;”i”;”k”;”e”;” “;”h”;”a”;”s”;” “;”$”;”3”;”0”;”0”;”0”;” “;”d”;”o”;”l”;”l”;”a”;”r”;”s”;””;””;””;””;””;””;””;””;}

We added 0 value after array because formula will display errors values whenever the character is the text:

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;”3”;”0”;”0”;”0”;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

IFERROR function replaces error values with an empty string:

{“”;“”;“”;“”;“”;“”;“”;“”;“”;“”;”3”;”0”;”0”;”0”;“”;“”;“”;“”;“”;“”;“”;“”;“”;“”;“”;“”;“”;“”;“”;“”}

Finally, TEXTJOIN function removes the empty strings and only numeric characters remain as a result: 3000.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

The data in column C has non printable characters before and after the data contained in each cell. In cell G2, enter the formula to remove all non printable characters
Solved by F. Q. in 20 mins
I need to count unique, non-numeric values from a dynamic column.
Solved by D. L. in 23 mins
im trying to solve regression on excel but it says " regression input range contains non-numeric data error" can you help me with this?
Solved by E. Y. in 11 mins

Leave a Comment

avatar