Home » Blogs » Nick's blog

85. Excel Tips - Remove Non Numeric Characters

Nick's picture
Submitted by Nick on 21 April, 2009 - 13:50


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:

remove-non-numeric-characters

Download sheet to practise how to Remove Non Numeric Characters in Excel

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

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

not really useful?!

Submitted by Anonymous on 22 August, 2011 - 18:51.

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

i'm continuing my google search

»

This will

Submitted by Johnson11 on 26 October, 2012 - 23:11.

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
3.) Add a SUBSTITUTE(
4.) And a ,"*") to remove a * or anything else included

»

...also

Submitted by Anonymous on 22 August, 2011 - 18:52.

your site said that it can "email me about replies to this comment" but there is no email field :-) just FYI

»
Nick's picture

substitute

Submitted by Nick on 24 August, 2011 - 08:04.

you need to create a user account and login..

for your case, you should use "substitute" function to replace only the characters you want to get rid of..

- if you know there's ( and ), just replace them

»