Formatting phone numbers in Excel

N

Nancy Lee

How do I change
(808) 123-4567
to
808-123-4567

I tried using custom formatting with ###-###-####, but it
won't apply unless I manually delete the parentheses.

I have over 35,000 phone numbers to change, please help!

Thanks,
Nancy Lee
 
M

Max

Assuming the numbers are in col A, A1 downwards, try putting in B1
:=REPLACE(TRIM(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")),4,1,"-")

Copy down col B
Then select col B and copy>paste special>values>ok in place to remove the
formula
 

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

Similar Threads

Multiple formats in one cell 1
Login Issues 0
Formatting Phone Numbers in Excel 2007 Help 16
Country Code (+1) in contact numbers 0
Conditional Formatting from a group 6
Parenthesis Phone Number 6
changing the Phone Number Input Mask to all for extensions 2
Contact Phone Number Auto Format 0

Top