Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 54
Default Phone number format on numbers with Extensions

Hi all,

I have a worksheet with imported data. One column contains phone numbers
that SOMETIMES have a three or four digit extension after them. Example of
exactly what it looks like:

9735551234
9731234443
9735559876473
97322292835743
9736758372
9734448573928
etc etc for thousands of rows

What I'd like to do is apply the custom phone number format so that they
look like phone numbers: i.e. (973) 555 -1234. Problem is, I can't apply it
to the entire column because those three or four digit extensions get in the
way.

My question - is there a way to remove that three or four digit extension
from the end of the phone number so I can apply a phone format to all the
cells at once?

Thanks everyone!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 461
Default Phone number format on numbers with Extensions

Say the first phone number is in cell A1... try this formula

="("&LEFT(A1,3)&") "&MID(A1,4,3)&"-"&MID(A1,7,4)

This will show it looking like the phone number you described.

Then to parse out the extension try this formula
=RIGHT(A1,LEN(A1)-10)



"Access Joe" wrote:

Hi all,

I have a worksheet with imported data. One column contains phone numbers
that SOMETIMES have a three or four digit extension after them. Example of
exactly what it looks like:

9735551234
9731234443
9735559876473
97322292835743
9736758372
9734448573928
etc etc for thousands of rows

What I'd like to do is apply the custom phone number format so that they
look like phone numbers: i.e. (973) 555 -1234. Problem is, I can't apply it
to the entire column because those three or four digit extensions get in the
way.

My question - is there a way to remove that three or four digit extension
from the end of the phone number so I can apply a phone format to all the
cells at once?

Thanks everyone!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 54
Default Phone number format on numbers with Extensions

PERFECT! Exactly what I needed! Thank you!

"akphidelt" wrote:

Say the first phone number is in cell A1... try this formula

="("&LEFT(A1,3)&") "&MID(A1,4,3)&"-"&MID(A1,7,4)

This will show it looking like the phone number you described.

Then to parse out the extension try this formula
=RIGHT(A1,LEN(A1)-10)



"Access Joe" wrote:

Hi all,

I have a worksheet with imported data. One column contains phone numbers
that SOMETIMES have a three or four digit extension after them. Example of
exactly what it looks like:

9735551234
9731234443
9735559876473
97322292835743
9736758372
9734448573928
etc etc for thousands of rows

What I'd like to do is apply the custom phone number format so that they
look like phone numbers: i.e. (973) 555 -1234. Problem is, I can't apply it
to the entire column because those three or four digit extensions get in the
way.

My question - is there a way to remove that three or four digit extension
from the end of the phone number so I can apply a phone format to all the
cells at once?

Thanks everyone!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 461
Default Phone number format on numbers with Extensions

No problem, glad I could help.

"Access Joe" wrote:

PERFECT! Exactly what I needed! Thank you!

"akphidelt" wrote:

Say the first phone number is in cell A1... try this formula

="("&LEFT(A1,3)&") "&MID(A1,4,3)&"-"&MID(A1,7,4)

This will show it looking like the phone number you described.

Then to parse out the extension try this formula
=RIGHT(A1,LEN(A1)-10)



"Access Joe" wrote:

Hi all,

I have a worksheet with imported data. One column contains phone numbers
that SOMETIMES have a three or four digit extension after them. Example of
exactly what it looks like:

9735551234
9731234443
9735559876473
97322292835743
9736758372
9734448573928
etc etc for thousands of rows

What I'd like to do is apply the custom phone number format so that they
look like phone numbers: i.e. (973) 555 -1234. Problem is, I can't apply it
to the entire column because those three or four digit extensions get in the
way.

My question - is there a way to remove that three or four digit extension
from the end of the phone number so I can apply a phone format to all the
cells at once?

Thanks everyone!

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
phone number format [email protected] Excel Worksheet Functions 2 January 21st 08 11:40 AM
phone number format Omar Excel Discussion (Misc queries) 3 January 21st 08 11:20 AM
How do I format phone numbers in excel? CBickley Excel Discussion (Misc queries) 11 August 23rd 06 11:24 PM
Format sequence for phone numbers? LACA Excel Discussion (Misc queries) 2 June 1st 06 01:44 AM
how do I add phone number format as a permanent custom format? frustratedagain Excel Discussion (Misc queries) 3 February 4th 06 04:52 AM


All times are GMT +1. The time now is 11:01 AM.

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"