remove text with format code for phone numbers

[Jan]

What format code is needed to remove the "()", spaces and "-" from "(555)
555-5555" that would look like "5555555555" ?

also from "555 555-5555" into "5555555555"
and "555 555 5555" into "5555555555"

 

[Sheeloo]

If you have the numbers with actual ()- and spaces then search for them ()-
one by one and replace...
If they are displayed with formatting then simply choose Number format
without decimals.

 

[Satti Charvak]

Hi Jan,
Use this formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"
",""),"-",""),"(",""),")","")


i presume the number is in cell A1,

this is basically a nested substiute formula to remove the characters (,),-
and spaces.

 

[Shane Devenshire]

Hi,

The format code you need is General if this is really a formatting issue.

Cheers,
Shane

 

[Jan]

wasn't looking for a formula.

Hi Jan,
Use this formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"
",""),"-",""),"(",""),")","")


i presume the number is in cell A1,

this is basically a nested substiute formula to remove the characters (,),-
and spaces.

--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India