View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Satti Charvak[_2_] Satti Charvak[_2_] is offline
external usenet poster
 
Posts: 53
Default remove text with format code for phone numbers

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


"Sheeloo" wrote:

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.

"Jan" wrote:

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"