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!