Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a column in excel 2003 that contacts phone numbers. Thousands of rows of phone numbers. Problem is they are not all formatted the same way, some are (123) 455-3983, some are 332.333.5555, etc. I need them all to be 123-456-7890. How can I do this?

Thanks!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

You can create a new column with the following formula...

=TEXT(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")," ",""),"-",""),".","")),"000-000-0000")

...then Copy + Paste Special -> Values and delete the original column.

How It Works:

  • The nested SUBSTITUTE() functions strip out any spaces, brackets, dots and dashes.
  • The VALUE() function converts the result from text to a number
  • The TEXT() function converts it back to text applying the format 000-000-0000. This nicely handles phone numbers beginning with one or more zeroes.

If your input contains any more unwanted formatting, use more nested SUBSTITUTE() functions to remove them.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.