Phone Number formatting

[ButchJ]

Hi,


I’m trying to format phone numbers from this

column A format: 123-123-1234 or (123) 12301234

to this column B format: (123) 123-1234


(Column A can have two formats)

(Column B can have only one format)


If a cell in column A is empty, the adjacent cell in column B should also be empty


This is the string I have been working with, but it sometimes leaves a “(” in column B if the phone number in column A is changed from one format to the other


=IF(ISBLANK(A3),"",IF((LEFT(A3,1)="("),A3,""&"("&IF(ISTEXT(A3),SUBSTITUTE(A3,"-",") ",1))))


Any assistance would be appreciated.


Thanks,

ButchJ

 

[Hui]

Butch


I find the easiest way to deal with phone numbers is not to worry about formatting, just use the straight numbers in Column A ie: 123467890 and then use formatting to fix the display of the number in your case try (###) ###-#### will give you (123) 456-78990

 

[ButchJ]

Thanks Hui,


That will work.