Hi everyone,
Is there a way to remove the non numerical characters and spaces in a phone number?
Sample: +808 788,909 to 808788909?
The data i am cleaning is consists of multiple non numerical numbers hence its taking time to manual remove.
Any thoughts on this?
Thanks!
One example is not much to work with, but if that string is in cell A2, you could use this formula (e.g. in B2):
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"+",""),",","")," ","")
Copy down as required.
Hope this helps.
Pete
another option if to use find and replace, find what + replace with leave blank. the replace all
if you don't have too many characters it should be pretty fast and won't need a helper column.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Two other ways.
I "borrowed" these formulas. The first from XOR LX (who says he got it from someone called Lori.)
You will need to array enter this.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Formula:
Please Login or Register to view this content.
It is my understanding that Harlan Grove created this one. Array entry not required.
The ROW($1:$25) was arbitrary. It just needs to be at least the length of the string in A1.Formula:Please Login or Register to view this content.
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks