Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Jan Jan is offline
external usenet poster
 
Posts: 159
Default remove text with format code for phone numbers

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"
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,805
Default remove text with format code for phone numbers

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"

  #3   Report Post  
Posted to microsoft.public.excel.misc
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"

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,346
Default remove text with format code for phone numbers

Hi,

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

Cheers,
Shane

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
Jan Jan is offline
external usenet poster
 
Posts: 159
Default remove text with format code for phone numbers

wasn't looking for a formula.

"Satti Charvak" wrote:

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"

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do you remove phone format???? TotallyConfused Excel Discussion (Misc queries) 2 October 30th 08 01:39 PM
Remove dashes from phone numbers in a spreadsheet AAH Excel Worksheet Functions 3 July 25th 08 06:50 AM
How can I remove extra characters from a list- phone numbers in ex Sarah HK Excel Worksheet Functions 2 November 28th 07 05:21 PM
Phone with/without area code format barnabas Excel Worksheet Functions 2 September 4th 07 07:15 PM
How do you remove duplicate phone numbers in an Excel spreadsheet Bridgette Excel Discussion (Misc queries) 1 May 25th 06 09:38 PM


All times are GMT +1. The time now is 07:22 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"