Help formatting please.

[cruisin4burgers]

So I have two columns, several pages long, full of phone numbers. Some
of the numbers in the columns look like this: (555) 555-5555 and about
half the other ones are lacking the parenthesis. IE 555-555-5555.


I want to make everything uniform by adding parenthesis to all the
numbers. How do I go about doing that?

Thanks.

 

[Dan E]

cruisin4burgers,

Assuming that ALL of the phone numbers are either
###-###-#### OR (###) ###-####

In an Empty column put (I used A1 as the start of data)
=IF(LEFT(A1,1)<>"(","(" & LEFT(A1,3) & ") " & RIGHT(A1,8),A1)
Copy down
This should give you all numbers like (###) ###-####

If you want to you can copy this row and then paste special as values
over your old numbers.

Dan E

 

[cruisin4burgers]

Well I am trying to get it so that all the phone numbers look the same.
I have about 55,000 numbers and some of them have a (555) for the area
code while the other half of the numbers do not have the parenthesis.
If I highlight the whole column then format number it doesn't add the
() onto the phone numbers.

So I was wondering if there was some way that I could highlight
everything then make it so that all the area codes have a () around
them.. Hopefully I am explaining this well enough. Here is what it
looks like now:
(555) 555-5555
555-555-5555
(555) 555-5555
555-555-5555 ect...

Here is what I want it to look like throughout the entire spreadsheet.
(555) 555-5555
(555) 555-5555
(555) 555-5555

I want all the phone numbers to look uniform and so far I cannot find a
way to format that.

That way when I am done I can just sort them by area code.

 

[cruisin4burgers]

Thanks! I'll go try that right now. I was just posting while you wrote
that up.

 

[Boxman]

*So I have two columns, several pages long, full of phone numbers.
Some of the numbers in the columns look like this: (555) 555-5555 and
about half the other ones are lacking the parenthesis. IE
555-555-5555.


I want to make everything uniform by adding parenthesis to all the
numbers. How do I go about doing that?

Thanks. *

If they all consist of 10 numbers in one or the other of these two
formats, put this conversion formula in the cell to the right of each
one. Assuming your first one is in cell A1 put this formula in B1

="("&IF(LEFT(A1)="(",MID(A1,2,3),MID(A1,1,3))&")"&" "&RIGHT(A1,8)

 

[cruisin4burgers]

It worked! Thanks!