Best Way to Format Phone Numbers

[Janespola]

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))))

 

[vletm]

cell B2 =IF(MID(A2:A100,4,1)="-","(" &SUBSTITUTE(A2:A100,"-",") ",1),SUBSTITUTE(A2:A100,"0","-"))
modify A100 as needed.

 

[petter_hoang]

cell b2=IF(A2<>"",IF(LEFT(A2)="(",REPLACE(A2,11,0,"-"),"("&SUBSTITUTE(A2,"-",") ",1)),"")

 

[Juniad]

MS 365
=TEXT(LEFT(REGEXREPLACE(A1,"\D",""),10),"(###) ###-####")

 

[rajeshac]

=IF(A1="", "", TEXTJOIN("",, "(", MID(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),1,3), ") ", MID(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),4,3), "-", MID(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),7,4)))