Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Jim Jim is offline
external usenet poster
 
Posts: 615
Default Convert a phone number

Excel experts,

how would I strip out the dash and space from this phone number, using a
formula:

(201) 256-2241

so that it looks like this:

(201)2562241

Thanks so much
Jim
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,942
Default Convert a phone number

hi
assuming phone numbers in column A and all have the same format....
=LEFT(A5,5)&MID(A5,7,3)&RIGHT(A5,4)

regards
FSt1

"Jim" wrote:

Excel experts,

how would I strip out the dash and space from this phone number, using a
formula:

(201) 256-2241

so that it looks like this:

(201)2562241

Thanks so much
Jim

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,696
Default Convert a phone number

you can just do a pair of finds... Find (space) replace with (blank) and find
- replace with (blank)

Or, if you must... =left(A1,5)&mid(A1,7,3)&right(A1,4)



"Jim" wrote:

Excel experts,

how would I strip out the dash and space from this phone number, using a
formula:

(201) 256-2241

so that it looks like this:

(201)2562241

Thanks so much
Jim

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Convert a phone number

Jim

Try this and drag down as required

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

Mike

"Jim" wrote:

Excel experts,

how would I strip out the dash and space from this phone number, using a
formula:

(201) 256-2241

so that it looks like this:

(201)2562241

Thanks so much
Jim

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
convert phone format Rusty Excel Discussion (Misc queries) 3 August 20th 09 05:44 AM
convert phone number andrewm Excel Discussion (Misc queries) 4 January 18th 09 10:22 PM
Convert number to phone number smartgal Excel Discussion (Misc queries) 2 October 16th 08 11:56 PM
Here we are with the phone numbers again. I need to convert Cody Kid Excel Worksheet Functions 7 February 27th 08 07:22 PM
Convert phone to *** manjunath Excel Worksheet Functions 2 March 30th 07 03:02 AM


All times are GMT +1. The time now is 08:17 PM.

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

About Us

"It's about Microsoft Excel"