Reformat numbers
 

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

Reformat numbers
 

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

Reformat numbers
 

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

Reformat numbers
 

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

Reformat numbers
 

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

Reformat numbers
 

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

Reformat numbers
 

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