set a default format for phone number

[mohsinali]

Can I set the default format for phone number?

I know the Format Cells feature of MS Excel. But I want some extensive kinda requirements. Like if user enter phone number +1 923-456-7890 then it should automatically formatted by removing extra spaces, special characters and the country code. And the new format should be (923) 456-7890.

If any help is there it'll be highly appreciated.


[TABLE="width: 90"]
<colgroup><col width="90"></colgroup><tbody>[TR]
[TD="class: xl63, width: 90"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Harishiyer]

Hi,


Replce G11 in the formula with your cell Reference

="("&MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G11," ",""),"+1",""),"-",""),1,3)&") "&MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G11," ",""),"+1",""),"-",""),4,3)&"-"&MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G11," ",""),"+1",""),"-",""),7,4)

 

[mohsinali]

Hi Harishiyer,
I know the formula.

But I want to set this in auto format so that the formula cannot be written in a repeated manner.