How to format this?

[mojo300]

I received this as a .cvs and I am trying to format the info in Column A to look like the info in Column B. When I try to format cells and Special and phone number it does nothing. I was wondering if there was a formula to help me with this. Thanks in advance.

Book3
	A	B	C	D
1	330-727-2707	(330) 727-2707
2	330-518-0459	(330) 518-0459
3	330-518-1262	(330) 518-1262
4	330-518-1577	(330) 518-1577
5	330-519-7052	(330) 519-7052
6	330-559-1695	(330) 559-1695
Sheet1

 

[Oaktree]

Select column A, use edit --> replace to replace - with nothing (leave the box blank)

Then, format column A as a phone number once the -'s are removed.

 

[mojo300]

sorry but i am not sure I understand what you mean

 

[Vangaard]

You can also try this formula in the cells:

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

 

[Oaktree]

sorry but i am not sure I understand what you mean

Hmm..which part was confusing?

 

[mojo300]

So you are saying to go and select the entire column A, I did, next go to edit and replace. When replace shows up I have to blank boxes this is the part that confuses me. I hit replace all and nothing happens because I am trying to replace nothing with nothing.

 

[Oaktree]

When replace shows up and you have two boxes, put a - (hyphen) in the first one and leave the second one blank. Then click "replace all".

Once that's done, you should be able to format column A as a phone number to get the desired result.

 

[mojo300]

Ok, how about I make this hopefully easier, what I am trying to do is use a vlook up formula but it does not recognize the numbers. So how do I get rid of all formating and just have the numbers basically show up like this.

Help With Formating FormulaMAIN.xls
	A	B	C	D
1	330-727-2707	3307272707
2	330-518-0459	3305180459
3	330-518-1262	3305181262
4	330-518-1577	3305181577
5	330-519-7052	3305197052
6	330-559-1695	3305591695
Sheet1

 

[mojo300]

It worked thank you very much all for your help

 

[daveatthewell]

It didn't work for me, but then Bill G's programmers only catered for "English USA" formatting - Format|Cells|number|special, then Locale (Eng (USA)), then Type|phone number! Maybe there's an add-in that caters for all the world's phone number patterns.

DK