• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Phone Number formatting

ButchJ

New Member
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

Excel Ninja
Staff member
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
 
Top