convert dashes to dots

[khatley]

Is there a way to convert dashes to dots, i.e. when I paste a phone number in a cell that has ###-###-#### or ########## I would like to convert it to ###.###.#### for consistency sake.

thanks

 

[Oaktree]

Try:

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")",""),".",""),3)&"."&MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")",""),".",""),4,3)&"."&RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")",""),".",""),4)

This should convert
xxx-xxx-xxxx
(xxx)xxx-xxxx
(xxx)xxxxxxx
xxxxxxxxxx
xxx.xxx.xxxx
(xxx)xxx.xxxx
etc.

all to xxx.xxx.xxxx

 

[xcellnoob]

You could use the substitute function in a neighboring column and then do a cut and paste special with values only.
in cell A1 : 564-568-5900

In cell B1 : =SUBSTITUTE(A1,"-",".")

 

[xcellnoob]

Try:

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")",""),".",""),3)&"."&MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")",""),".",""),4,3)&"."&RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")",""),".",""),4)

This should convert
xxx-xxx-xxxx
(xxx)xxx-xxxx
(xxx)xxxxxxx
xxxxxxxxxx
xxx.xxx.xxxx
(xxx)xxx.xxxx
etc.

all to xxx.xxx.xxxx

Well sure, if you want to get all Complete and everything.

 

[Scott Huish]

Perhaps:

=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ",""),".","")+0,"[<=9999999]###\.####;###\.###\.####")