Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
phone number format | Excel Worksheet Functions | |||
phone number format | Excel Discussion (Misc queries) | |||
How do I format phone numbers in excel? | Excel Discussion (Misc queries) | |||
Format sequence for phone numbers? | Excel Discussion (Misc queries) | |||
how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) |