Reformatting All Numbers in a Column

[pclutts]

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.

 

[SteveO59L]

Will it always be 4 characters - 2 characters - 3 characters ?

 

[jdc]

Hi,
Just select the column and Format, pick Custom and type 0000-00-000 in the box.

HTH

John

 

[pclutts]

Yes. It will always be 4 characters - 2 characters - 3 characters.

 

[SteveO59L]

Either use jdc's suggestion, or something along the lines of

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

 

[pclutts]

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.