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

try:

A1=number

in B1:


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

"EllenM" wrote:

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

One way:

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


In article ,
EllenM wrote:

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

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"EllenM" wrote in message
...
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

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" wrote:

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

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"EllenM" wrote in message
...
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

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"EllenM" wrote in message
...
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