Function to uniformerly format different phone numbers

A

Access Joe

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!
 
G

Gaurav

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?
 
G

Gaurav

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),""))
 
A

Access Joe

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.
 
G

Gaurav

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),""))
 
A

Access Joe

PERFECT! YOU ROCK - thanks!

Gaurav said:
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),""))
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

You can Custom Format the column using this pattern...

[<=9999999]000-0000;(000) 000-0000

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel "Local" phone numbers in a list do not include area code. How to add? 2
Here we are with the phone numbers again. I need to convert 7
Adding area codes only to phone numbers missing an area code 4
Formatting phone numbers using Special or Custom ..Help!!!! 2
Format phone numbers to look the same 1
How do I add an area code to a list of phone numbers in Excel? 2
Formula to change Phone Number format 6
Adding numbers to several cells at one time 4

Top