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!

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!

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!

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!

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!

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!

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!

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!