Reformat numbers

[EllenM]

Hello,
I have a bunch of 9 digit numbers in a column that I'd like reformatted.
I'd like to remove the leading zeros and put the remaining numbers in
xxxxxx-xx-x format. For instance:
123456789 becomes 123456-78-9
000012345 becomes 12-34-5

Thanks in advance,
Ellen

 

[Toppers]

try:

A1=number

in B1:


=TEXT(A1,"######-##-#")

 

[JE McGimpsey]

One way:

Format/Cells/Number/Custom #0-00-0

 

[Bernard Liengme]

I use Custom Format #-##-# to get
123456789 to 123456-78-9
12345 to 12-34-5

But I do not understand you reference to leading zeros perhaps you already
have custom format to get them
best wishes

 

[bj]

do you want just the display to have dashes or what is actually in the cell
for display format-cell-number-custom
0-00-0
this assumes what is in the cell is really a number and not text.

 

[EllenM]

Thanks to everyone who answered my question. All your quick responses worked!!

 

[EllenM]

Hi Bernard,
As for your mention of the leading zeros, the data is in the format of text,
I think. They've got a green triangle in the upper left side of the cell.
My first step was to convert the data to number with multiply in the paste
special box.

Ellen