Reformatting All Numbers in a Column

pclutts

New Member
Joined
Dec 6, 2005
Messages
3
I need to change all numbers in a column from 012345678 to 0123-45-678. Can anyone tell me how to do that globally instead of changing them one at a time? Thanks in advance for the help.
 

Some videos you may like

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.

jdc

Active Member
Joined
Nov 23, 2004
Messages
263
Hi,
Just select the column and Format, pick Custom and type 0000-00-000 in the box.

HTH

John
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
Either use jdc's suggestion, or something along the lines of

=left(a1,4)&"-"&mid(a1,5,2)&"-"&right(a1,3)
 

pclutts

New Member
Joined
Dec 6, 2005
Messages
3
I used jdc's suggestion and it worked. However, leading zeros where removed, and I need to keep them. Also, I have to double-click on every individual cell to get the reformatting to take affect. I wanted to be able to make this change globally. Any other help is appreciated. Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,536
Messages
5,572,774
Members
412,482
Latest member
arooshrana2
Top