Formula: Change multiple Phone #'s to correct format

I have a LOT of phone numbers (A1:A1000) I need formatted the same using a formula in the row to the right (B1:B1000), is it possible?

The problem is that there are several variations of the phone numbers in the column.

1) "###-###-####"
2) "(###)###-####"
3) "###-####"
4) "##########"
5) "(###)-###-####)"

Examples of above:

A1) 704-555-5555
A2) (704)234-2343
A3) 445-2343
A4) 7044343333
A5) (704)-434-4444

I'd like to have each one look like this: (704) 444-4444. or (###) ###-####. If the number is only 7 digits because the area code is missing, I want it to automatically add 704 to the beginning.

My goal is to make Column B look like this:

B1) (704) 555-5555
B2) (704) 234-2343
B3) (704) 445-2343
B4) (704) 434-3333
B5) (704) 434-4444

Is there a simple way to use a formula for this instead of a macro? The data frequently changes in Column A so copying/pasting isn't an option.

Originally Posted by zbor

Here, try this:

=TEXT(RIGHT(SUBSTITUTE(A2,"-",""),7),"(704) "&"000"&"-"&"0000")

That worked GREAT! Mostly...

One thing it did was change ALL area codes to 704. I only want those without one already to have it added automatically, not change those already there.

After seeing the clean data, i realized I also missed one format: ### ### #### (Basically, no separators at all) Can you modify it to work with this one too? 704 444 4444 is an example

Originally Posted by zbor

that will slightly complicate thing:

The area code thing is working great now thanks. Last thing is to change the final format which didn't seem to get fixed in this last string you sent which was:

=IF(LEN(A2)<10,TEXT(RIGHT(SUBSTITUTE(A2,"-",""),7),"(704) "&"000 0000"),TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"(",""),")",""),"("&"000"&") "&"000 0000"))

I need to change 704 333 3333 to (704) 333-3333

Do me the favor or replying to my post twice so I can be your 5,000th customer