Phone Number Formating

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.
 
F

Frank Kabel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top