# 85. Excel Tips - Remove Non Numeric Characters

This tip is about removing non-numeric characters from a cell's value:

1. The way we do it is to use a combination of functions:
• ROW
• The ROW function tells us what row we are in
• CHAR
• The CHAR function returns a character
• There are 255 characters that can be returned by the CHAR function
2. The next thing to do is to substitute out all the characters that are not 1 to 9
3. This leaves the numeric characters

Here's a screen shot of our data in Excel:

Training Video on how to Remove Non Numeric Characters in Excel:

AttachmentSize
remove-non-numeric-characters.xls54.5 KB

### not really useful?!

I needed to know how to remove non-numeric characters from over 51355 rows of data (phone numbers with random characters like ( or )!) I'd have to do this 51355 times to get rid of all the characters in the entire column!

surely Excel has a way to do regular expressions :-)

### This will

This will work:

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

1.) Paste this into your cell (Change E99 to cell in question)
2.) Go to your cell and press F2
3.) Press CNTL/SHIFT/ENTER

OR

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E96,"A",""),"B",""),"C",""),"D",""),"E",""),"F",""),"G",""),"H",""),"I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O",""),"P",""),"Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W",""),"X",""),"Y",""),"Z",""),"a",""),"b",""),"c",""),"d",""),"e",""),"f",""),"g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m",""),"n",""),"o",""),"p",""),"q",""),"r",""),"s",""),"t",""),"u",""),"v",""),"w",""),"x",""),"y",""),"z",""),",",""),"-",""),"'",""))

1.) This will leave your numbers as text
2.) Change E96 to cell in question