Here we are with the phone numbers again. I need to convert

[Cody Kid]

In 2006 Pete_UK responded to Disaster lady regarding changing a large column
of telephone numbers formated as (xxx) xxx-xxxx plus some of the phone
numbers did not have an area code. She wished her entire column to be
formatted as follows:
1-xxx-xxx-xxxx.

I have a similar problem except I want my xxx-xxx-xxxx phone numbers to be
converted to (xxx) xxx-xxxx. Plus I have many phone numbers with no area
code. Pete resolved Disaster Lady's problem with the following formula:

=IF(LEFT(AE2,1)="(","1-"&MID(AE2,2,3)&"-"&RIGHT(AE2,8),"1-585-"&AE2)

and copy down to AM2500. This assumes the phone numbers are in the two
formats described, and works by examining the first character of the
number - if it is "(", as in "(716) 555-1212", this will be converted
to "1-716-555-1212", otherwise it will have "1-585-" appended to the
beginning of it.

If you want these converted numbers to replace the ones you have, then
highlight the cells AM2:AM2500, click <copy>, then Edit | Paste Special
| Values (check) | OK then <Enter> - this will have fixed the values in
column AM. You could then <cut> these values and <paste> them to
overwrite the values in column AE.

Hope this helps.

Pete

I am not experienced enough to understand this formula to make the changes
to convert my columns to the proper format. Can someone help?

Thanks - CodyKid

 

[Gary''s Student]

=IF(LEN(A1)=8,A1,"("&LEFT(A1,3)&") "&RIGHT(A1,8))

So 123-456-7890 will return (123) 456-7890
and
for no area code 456-7890 will return 456-7890 (same thing)

 

[Cody Kid]

Gary,

Thank you so much. Now is there a formula that will put the area code 406
on all the phone numbers with no area code?

This is great, I am going to go try it right now.
Thanks, Cody

 

[Gord Dibben]

=IF(LEN(A1)=8,"(406)"&A1,"(" & LEFT(A1,3)&") "&RIGHT(A1,8))


Gord Dibben MS Excel MVP

 

[Cody Kid]

Hello again Gary,

Alas, it did not work. Obviously I did not have the steps down correctly. I
copied the formula you gave me into a cell in a blank column (Q) next to the
phone numbers (P). Then I copied it down say 50 lines as a test. I then
did a cut and paste (per Pete's instructions) from column Q to Column P. It
just gave me the same ( ) with a number that was in Q. Obviously I missed a
step or didn't give you enough or correct informaiton.

Also, Is there a way in your formula, as with Pete's, to add the area code
to the phone numbers that do not have one?

Thank you

 

[Cody Kid]

Thank you Gord, I will try yours as I can see it is putting in the area code.
Maybe it will make a difference or maybe I don't know what i am doing yet?
Thanks

 

[Gary''s Student]

Here is the new formula:

=IF(LEN(A1)=8,"(406) " & A1,"("&LEFT(A1,3)&") "&RIGHT(A1,8))

It assumes your input is in A1

 

[Cody Kid]

Ahhh, Gary it worked. I think I just failed to change the cell #'s. There
is one item I would like to add to that formula. We import worksheets from
various places and everyone wants to do it different. So we end up no zip -
(got that solved) - no parenthesis around the area code (got that solved) but
if an area code already has the ( ) around the area code - the phone number
comes up incomplete. If there is no fix to that, I can always sort and only
do the phone numbers that need the correction, then sort it back the way we
want it. But if there is a fix or whatever to add to that formula that would
be great.

Thank you so much - how do you learn how to write these formula's

Cody