| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to uniformerly format different phone numbers
Hi everyone,
I have a situation where a column contains phone numbers, but all numbers are written differently. Some have areas codes and some don't. For example: COLUMN A 5742229384 5593857382 2283018 2201929184 3324838 SEE -some have the area code listed, and some don't. Is there a way to format the entire column so they look like this: (574) 222-9384 (559) 385-7382 228-3018 (220) 192-9184 332-4838 I'm familiar with the input mask found in Format cells / special, but it doesn't give me the right results because some phone numbers don't have an area code. Can anyone help? Thanks so much! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to uniformerly format different phone numbers
Assuming your first number is in A1. Enter the following in B1
=IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,3),IF(LEN(A1)=7,LEFT(A1,3)&"-"&RIGHT(A1,4),"")) Drag it down as far as needed. Then if you want you can copy column B and paste special values in column A. Delete column B. Does that help? "Access Joe" wrote in message ... Hi everyone, I have a situation where a column contains phone numbers, but all numbers are written differently. Some have areas codes and some don't. For example: COLUMN A 5742229384 5593857382 2283018 2201929184 3324838 SEE -some have the area code listed, and some don't. Is there a way to format the entire column so they look like this: (574) 222-9384 (559) 385-7382 228-3018 (220) 192-9184 332-4838 I'm familiar with the input mask found in Format cells / special, but it doesn't give me the right results because some phone numbers don't have an area code. Can anyone help? Thanks so much! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to uniformerly format different phone numbers
Small correction.
=IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,4),IF(LEN(A1)=7,LEFT(A1,3)&"-"&RIGHT(A1,4),"")) "Gaurav" wrote in message ... Assuming your first number is in A1. Enter the following in B1 =IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,3),IF(LEN(A1)=7,LEFT(A1,3)&"-"&RIGHT(A1,4),"")) Drag it down as far as needed. Then if you want you can copy column B and paste special values in column A. Delete column B. Does that help? "Access Joe" wrote in message ... Hi everyone, I have a situation where a column contains phone numbers, but all numbers are written differently. Some have areas codes and some don't. For example: COLUMN A 5742229384 5593857382 2283018 2201929184 3324838 SEE -some have the area code listed, and some don't. Is there a way to format the entire column so they look like this: (574) 222-9384 (559) 385-7382 228-3018 (220) 192-9184 332-4838 I'm familiar with the input mask found in Format cells / special, but it doesn't give me the right results because some phone numbers don't have an area code. Can anyone help? Thanks so much! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to uniformerly format different phone numbers
AWESOME GAURAV! Works great! Just one small thing I forgot to ask: in the
situations where there is NO area code entered, can I have that area code automatically added (it would always be the same thing - a "318" area code).So following the below example, the result would be: (574) 222-9384 (559) 385-7382 (318) 228-3018 (220) 192-9184 (318) 332-4838 Thanks again. This is great. "Gaurav" wrote: Small correction. =IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,4),IF(LEN(A1)=7,LEFT(A1,3)&"-"&RIGHT(A1,4),"")) "Gaurav" wrote in message ... Assuming your first number is in A1. Enter the following in B1 =IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,3),IF(LEN(A1)=7,LEFT(A1,3)&"-"&RIGHT(A1,4),"")) Drag it down as far as needed. Then if you want you can copy column B and paste special values in column A. Delete column B. Does that help? "Access Joe" wrote in message ... Hi everyone, I have a situation where a column contains phone numbers, but all numbers are written differently. Some have areas codes and some don't. For example: COLUMN A 5742229384 5593857382 2283018 2201929184 3324838 SEE -some have the area code listed, and some don't. Is there a way to format the entire column so they look like this: (574) 222-9384 (559) 385-7382 228-3018 (220) 192-9184 332-4838 I'm familiar with the input mask found in Format cells / special, but it doesn't give me the right results because some phone numbers don't have an area code. Can anyone help? Thanks so much! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to uniformerly format different phone numbers
Yes you can.
=IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,4),IF(LEN(A1)=7,"("&318&")"&" "&LEFT(A1,3)&"-"&RIGHT(A1,4),"")) "Access Joe" wrote in message ... AWESOME GAURAV! Works great! Just one small thing I forgot to ask: in the situations where there is NO area code entered, can I have that area code automatically added (it would always be the same thing - a "318" area code).So following the below example, the result would be: (574) 222-9384 (559) 385-7382 (318) 228-3018 (220) 192-9184 (318) 332-4838 Thanks again. This is great. "Gaurav" wrote: Small correction. =IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,4),IF(LEN(A1)=7,LEFT(A1,3)&"-"&RIGHT(A1,4),"")) "Gaurav" wrote in message ... Assuming your first number is in A1. Enter the following in B1 =IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,3),IF(LEN(A1)=7,LEFT(A1,3)&"-"&RIGHT(A1,4),"")) Drag it down as far as needed. Then if you want you can copy column B and paste special values in column A. Delete column B. Does that help? "Access Joe" wrote in message ... Hi everyone, I have a situation where a column contains phone numbers, but all numbers are written differently. Some have areas codes and some don't. For example: COLUMN A 5742229384 5593857382 2283018 2201929184 3324838 SEE -some have the area code listed, and some don't. Is there a way to format the entire column so they look like this: (574) 222-9384 (559) 385-7382 228-3018 (220) 192-9184 332-4838 I'm familiar with the input mask found in Format cells / special, but it doesn't give me the right results because some phone numbers don't have an area code. Can anyone help? Thanks so much! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to uniformerly format different phone numbers
PERFECT! YOU ROCK - thanks!
"Gaurav" wrote: Yes you can. =IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,4),IF(LEN(A1)=7,"("&318&")"&" "&LEFT(A1,3)&"-"&RIGHT(A1,4),"")) "Access Joe" wrote in message ... AWESOME GAURAV! Works great! Just one small thing I forgot to ask: in the situations where there is NO area code entered, can I have that area code automatically added (it would always be the same thing - a "318" area code).So following the below example, the result would be: (574) 222-9384 (559) 385-7382 (318) 228-3018 (220) 192-9184 (318) 332-4838 Thanks again. This is great. "Gaurav" wrote: Small correction. =IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,4),IF(LEN(A1)=7,LEFT(A1,3)&"-"&RIGHT(A1,4),"")) "Gaurav" wrote in message ... Assuming your first number is in A1. Enter the following in B1 =IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,3),IF(LEN(A1)=7,LEFT(A1,3)&"-"&RIGHT(A1,4),"")) Drag it down as far as needed. Then if you want you can copy column B and paste special values in column A. Delete column B. Does that help? "Access Joe" wrote in message ... Hi everyone, I have a situation where a column contains phone numbers, but all numbers are written differently. Some have areas codes and some don't. For example: COLUMN A 5742229384 5593857382 2283018 2201929184 3324838 SEE -some have the area code listed, and some don't. Is there a way to format the entire column so they look like this: (574) 222-9384 (559) 385-7382 228-3018 (220) 192-9184 332-4838 I'm familiar with the input mask found in Format cells / special, but it doesn't give me the right results because some phone numbers don't have an area code. Can anyone help? Thanks so much! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to uniformerly format different phone numbers
Thanks for the feedback.
"Access Joe" wrote in message ... PERFECT! YOU ROCK - thanks! "Gaurav" wrote: Yes you can. =IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,4),IF(LEN(A1)=7,"("&318&")"&" "&LEFT(A1,3)&"-"&RIGHT(A1,4),"")) "Access Joe" wrote in message ... AWESOME GAURAV! Works great! Just one small thing I forgot to ask: in the situations where there is NO area code entered, can I have that area code automatically added (it would always be the same thing - a "318" area code).So following the below example, the result would be: (574) 222-9384 (559) 385-7382 (318) 228-3018 (220) 192-9184 (318) 332-4838 Thanks again. This is great. "Gaurav" wrote: Small correction. =IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,4),IF(LEN(A1)=7,LEFT(A1,3)&"-"&RIGHT(A1,4),"")) "Gaurav" wrote in message ... Assuming your first number is in A1. Enter the following in B1 =IF(LEN(A1)=10,"("&LEFT(A1,3)&")"&" "&MID(A1,4,3)&"-"&RIGHT(A1,3),IF(LEN(A1)=7,LEFT(A1,3)&"-"&RIGHT(A1,4),"")) Drag it down as far as needed. Then if you want you can copy column B and paste special values in column A. Delete column B. Does that help? "Access Joe" wrote in message ... Hi everyone, I have a situation where a column contains phone numbers, but all numbers are written differently. Some have areas codes and some don't. For example: COLUMN A 5742229384 5593857382 2283018 2201929184 3324838 SEE -some have the area code listed, and some don't. Is there a way to format the entire column so they look like this: (574) 222-9384 (559) 385-7382 228-3018 (220) 192-9184 332-4838 I'm familiar with the input mask found in Format cells / special, but it doesn't give me the right results because some phone numbers don't have an area code. Can anyone help? Thanks so much! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to uniformerly format different phone numbers
You can Custom Format the column using this pattern...
[<=9999999]000-0000;(000) 000-0000 Rick "Access Joe" wrote in message ... Hi everyone, I have a situation where a column contains phone numbers, but all numbers are written differently. Some have areas codes and some don't. For example: COLUMN A 5742229384 5593857382 2283018 2201929184 3324838 SEE -some have the area code listed, and some don't. Is there a way to format the entire column so they look like this: (574) 222-9384 (559) 385-7382 228-3018 (220) 192-9184 332-4838 I'm familiar with the input mask found in Format cells / special, but it doesn't give me the right results because some phone numbers don't have an area code. Can anyone help? Thanks so much! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format phone numbers to look the same | Excel Discussion (Misc queries) | |||
| Phone number format on numbers with Extensions | Excel Worksheet Functions | |||
| How do I format phone numbers in excel? | Excel Discussion (Misc queries) | |||
| Format sequence for phone numbers? | Excel Discussion (Misc queries) | |||
| Words > Numbers (i.e. Vanity Phone Numbers) function | Excel Worksheet Functions | |||
Linear Mode
