Need quick answer simple question

Beachson

Active Member
Joined
Oct 28, 2009
Messages
468
I have a column of phone number and instead of 1112223333 I would like to make every number look like this (111) 222-3333...how do I do this quickly
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

=TEXT(A1,"[<=9999999]###-####;(###) ###-####")

Then you can copy / paste special / values
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

c_m_s_jr

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
I know it sounds wierd someone just asked me for this and I formatted it but the format is not neccesary all the data is typed in already I just want to put the ( ) and - into them all


If you just want the LOOK of () and - then use the format option in Excel because this will show the () and - but the cell value will remain unchanged, thus keeping the number in tact as a number (I recommend this).

If you want to physically ADD the () and - then use the formula I provided and then copy and paste special values over the column with the 1112223333 (Though I don't recommend this).
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
Excel Workbook
G
14(289) 847579
15(289) 847579
16(289) 847579
17(289) 847579
18(289) 847579
19(289) 847579
20(289) 847579
21(289) 847579
22(289) 847579
23(289) 847579
24(289) 847579
25(289) 847579
26(289) 847579
27(289) 847579
28(289) 847579
29(289) 847579
Sheet15


If you highlight all your cells, then in the formula bar insert your mouse to where you want the first symbol. Then with the cells still highlighted, go to the insert ribon, then insert special symbol icon (looks like an upside down horse shoe and click on the first side of the parenthesis from the symbols shown. Press insert, then close and then control and enter. All the numbers will have opnbe side now do the same for the second side, and if you want to enter a space do the same and on the symbols menue theres a blank white cell in there. Click on that to add the space, again press insert then close and ctrl and enter to put the space into all your cells.

It's fairly quick way, abount 9 mouse clicks. I got Excel 2007 so not sure where that menu sits in other versions
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919

ADVERTISEMENT

Actuall forget all tha stuff i just typed-if you highlight your data, then in the formula bar just enter the correct symbols and hyphon where you want them and press ctrl and enter it will apply it to all of them. kinda mucks it up as a useable number though after that

Excel Workbook
GH
14(289)-847579
15(289)-847579
16(289)-847579
17(289)-847579
18(289)-847579
19(289)-847579
20(289)-847579
21(289)-847579
22(289)-847579
23(289)-847579
24(289)-847579
25(289)-847579
26(289)-847579
27(289)-847579
28(289)-847579
29(289)-847579
Sheet15
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
Excel Workbook
GH
14(289)-847579
15(289)-847579
16(289)-847579
17(289)-847579
18(289)-847579
19(289)-847579
20(289)-847579
21(289)-847579
22(289)-847579
23(289)-847579
24(289)-847579
25(289)-847579
26(289)-847579
27(289)-847579
28(289)-847579
29(289)-847579
Sheet15


i tried posting this and it got stopped.....but just highlight all your cells, in the formula bar add the parenthesis where you want them and a hyphon as well if you want, then ctrl and enter to apply that to all of your data.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,201
Messages
5,623,341
Members
415,968
Latest member
Chabal74

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