Thursday, September 18, 2008

Excel Fix a Phone list

Occasionally, you'll have a list of phone numbers that need a little fixing. This code fixes many things as long as the last 8 digits are like 555-1212 and there's three digits of area code either preceded by "(" or flush left as in "(555)555-1212" or "555-555-1212". The result will always be formatted as (555) 555-1212, unless an area code is given.

A2 represents where the broken number is stored.

=CONCATENATE("(",IF(LEN(A2)<9,"555",IF(LEFT(A2,1)="(",MID(A2,2,3),LEFT(A2,3))),") ",RIGHT(A2,8))

You should change 555 to your local area code.