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

"Gary''s Student" wrote:

=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)
--
Gary''s Student - gsnu200770


"Cody Kid" wrote:

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