Add Dashes to Telephone Numbers

dpmicka

Board Regular
Joined
Jun 13, 2002
Messages
122
I have a long column with 10 digit numbers. They are phone numbers and I'd like to add dashes to the appropriate place all through the column. For example, right now I have 1234567890 and I want it to become 123-456-7890. Is there a formula or simple code to do this for all numbers in that particular column?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Book1
ABCD
112345678901-234-678-7890
Sheet1


The formula looks like it's coming up funky on the html, so here it is.
=LEFT(A1,1)&"-"&MID(A1,2,3)&"-"&MID(A1,6,3)&"-"&RIGHT(A1,4)
 
Upvote 0
How about changing the cell format.
Hightlight the column
Go to Format>Cells>Custom and type:

###-###-####
 
Upvote 0
The formulas work great! I tried the custom format and it would work for new numbers I keyed in, but for some reason wouldn't work on existing data. No worries though - the formulas did the trick - thanks!
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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