Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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:
Solution examples
hi, my vlookup wont work in some rows but does in others. its temperemental. e.g it will work on rows 10 to 15, then not row 16 and 17, but the will for the rest.
Solved by K. Q. in 21 mins
Hi, What formula is best to sum 2 values in 2 separate cells based on a matched criteria? Thanks
Solved by A. Y. in 40 mins
I need to create a rows in Sheet 1 where Sheet 2 column c has values in Sheet 2 column D that are greater than .01 and leave out items in Sheet 1 where Sheet 3 column D are = 0 (without using a filter, then copy/paste).
Solved by I. Y. in 21 mins
Can you re edit this formula for me =IFERROR(IF(LEN(MID("RHS",1,FIND("RHS",B4,1)))=1,LEFT(B4,3),MID("RHS",1,FIND("RHS",B4,1))),MID("LHS",1,FIND("LHS",B4,1)))
Solved by S. L. in 40 mins
I need a formula that looks for a specific name in three columns, takes all of the rows that match to get dates from another column. Then from the dates that match the rows with the names, take the second earliest date and generate it. The below formula is giving me the second date listed for the specific person instead of giving me the second earliest date. =IFERROR(INDEX('Input Data'!$E$5:$E$150,SMALL(IF(Tracker!$B6='Input Data'!$G$5:$I$150,ROW('Input Data'!$G$5:$I$150)-ROW('Input Data'!$G$5)+1),2)), "N/A")
Solved by A. J. in 34 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc