How to format this?

mojo300

Active Member
Joined
Apr 18, 2006
Messages
301
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
ABCD
1330-727-2707(330) 727-2707
2330-518-0459(330) 518-0459
3330-518-1262(330) 518-1262
4330-518-1577(330) 518-1577
5330-519-7052(330) 519-7052
6330-559-1695(330) 559-1695
Sheet1
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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.
 

Vangaard

New Member
Joined
Oct 19, 2005
Messages
35
You can also try this formula in the cells:

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

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365

ADVERTISEMENT

sorry but i am not sure I understand what you mean

Hmm..which part was confusing?
 

mojo300

Active Member
Joined
Apr 18, 2006
Messages
301
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365

ADVERTISEMENT

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

Active Member
Joined
Apr 18, 2006
Messages
301
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
ABCD
1330-727-27073307272707
2330-518-04593305180459
3330-518-12623305181262
4330-518-15773305181577
5330-519-70523305197052
6330-559-16953305591695
Sheet1
 

daveatthewell

New Member
Joined
Jul 28, 2006
Messages
43
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
 

Forum statistics

Threads
1,136,347
Messages
5,675,233
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top