Function to uniformerly format different phone numbers

  • Thread starter Thread starter Access Joe
  • Start date Start date
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!
 
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?
 
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),""))
 
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.
 
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),""))
 
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...
 
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
Changing the format of a phone number 2
concatenating and formatting area code and phone number columns 4
Here we are with the phone numbers again. I need to convert 7
Phone Number Formats 2
phone number formatting for dummies 10
Formatting phone numbers using Special or Custom ..Help!!!! 2
formatting a phone number in a string 2

Back
Top