85. Excel Tips - Remove Non Numeric Characters
This tip is about removing non-numeric characters from a cell's value:
- 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
- ROW
- The next thing to do is to substitute out all the characters that are not 1 to 9
- 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:
| Attachment | Size |
|---|---|
| remove-non-numeric-characters.xls | 54.5 KB |
»
- Nick's blog
- Add new comment
- 7849 reads
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
...also
your site said that it can "email me about replies to this comment" but there is no email field :-) just FYI
substitute
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