Skip navigation

Stripping the formatting out of a phone number

228 Views 2 Replies Latest reply: Nov 16, 2012 5:02 PM by Mike2Simpson RSS
Mike2Simpson Calculating status...
Currently Being Moderated
Nov 16, 2012 12:39 PM

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

1234567890

1234567890

1234567890

1234567890

1234567890

1234567890

Numbers, OS X Mountain Lion (10.8.2)
  • Wayne Contello Level 6 Level 6 (12,800 points)
    Currently Being Moderated
    Nov 16, 2012 2:55 PM (in response to Mike2Simpson)

    this may help:

     

    Screen Shot 2012-11-16 at 4.55.23 PM.png

     

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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.