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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,356
Messages
6,136,079
Members
449,988
Latest member
Mabbas

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
Back
Top