Stripping the formatting out of a phone number

Question

Mike2Simpson Author

Level 1

0 points

Stripping the formatting out of a phone number

If you have a column of phone numbers all written in differently. As and example:

1234567890

123.456.7890

123-456-7890

123 456 7890

(123) 456.7890

(123)4567890

123 456789 0

How would you strip all of the formating out of the numbers so that it could be reformatted consistently?

In other words, how would you make the example above look like what's below?

1234567890

Numbers-OTHER, OS X Mountain Lion (10.8.2)

Posted on Nov 16, 2012 12:39 PM

Reply

Answer 1

Best reply

Wayne Contello

Level 7

26,529 points

Nov 16, 2012 2:55 PM in response to Mike2Simpson

this may help:

B1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","")," ",""),")",""),"(","")

Reply

Answer 2

Mike2Simpson Author

Level 1

0 points

Nov 16, 2012 5:02 PM in response to Wayne Contello

Thanks wayne for the clarification. That's basically what I had to do. Though as you can imagine if there are, not only "-"'s and parentheses, but periods, pluses, stars, pound signs and ampersands, the function could get a little messy to the user.

The only thing I could think of are that was to reference each of the SUBSTITUTED character out to a different column so that if wierd symbols arise they can just be enter in and referenced in the the long and messy SUBSTITUTE function.

Works well,

Thanks

Reply