adding area code to phone numbers

So I have an old spreadsheet with a lot of 7 digit phone numbers in a column (like 555-5555). I need to figure out how to ID all numbers using the ***-**** format and prefacing them with the area code 510 so it adds the area code in front of the number. There are some entries that have different area codes (415, 925, et al) or already have the 510 area code which will need to be ignored.

How do I accomplish this?

If the format is 510-555-5555, you can use =IF(LEN(A1)=12,A1,"510-"&A1)

When the value of A1 is zero (where a phone number isn't available) it returns a 510-0. Is there a way it can just leave the field blank if A1 is zero?

=if(a1="","",if(len(a1)=12,a1,"510-"&a1))

Originally Posted by darkyam

=if(a1="","",if(len(a1)=12,a1,"510-"&a1))

Hmmm, still returns a 510-0

Is A1 blank or 0? If 0, you'll have to replace "" with 0 in the first part of the formula.

Thanks!