Phone Number Formating

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings Everyone

Say I'm trying to compare two lists (member phone number lists essentially) for accuracy. One list (my reliable list) has the phone numbers entered as numbers, which I then set under the Special section in Formatting as Phone Numbers. They show up like so

(555) 555-555

The second list (the one I'd like to check out) has the numbers entered as text, like so

(555)555-555

How would I go about converting this text to the Special Phone Number format? Normally if the phone number is entered as text, I'm prompted by Excel to change the format to Number. However, with the parentheses added Excel doesn't prompt me to do so.
 
Hi Rob
for the second data use a helper column (lets say column B) and enter
the following formula
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")
and copy down.
after this format this column as telephone number
 
Back
Top