85. Excel Tips - Remove Non Numeric Characters

Nick's picture

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:


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

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

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

i'm continuing my google search

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



1.) This will leave your numbers as text
2.) Change E96 to cell in question
4.) And a ,"*") to remove a * or anything else included


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

Nick's picture


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