Insert Leading Zeros
 

I have a data field that I want to instert a leading zero (where necessary)
so that I may sort the data correctly. Is there an easy way to do this?

My service codes currently sort as follows:

32-1
32-10
32-11
32-12
32-2
32-20
32-21
32-3

Is there formula that will insert a leading zero into the 32-1, 32-2, 32-3
etc. to make it 32-01, 32-02, 32-03?

Thanks for the help.

Hi
you may try the following in a helper column:
=LEFT(A1,FIND("-",A1) & TEXT(--(MID(A1,FIND("-",A1),10)),"00")

--
Regards
Frank Kabel
Frankfurt, Germany

"hkslater" schrieb im Newsbeitrag
...
I have a data field that I want to instert a leading zero (where
necessary)
so that I may sort the data correctly. Is there an easy way to do
this?

My service codes currently sort as follows:

32-1
32-10
32-11
32-12
32-2
32-20
32-21
32-3

Is there formula that will insert a leading zero into the 32-1, 32-2,
32-3
etc. to make it 32-01, 32-02, 32-03?

Thanks for the help.

Hi
or another alternative (a little bit shorter):
=TEXT(--SUBSTITUTE(A1,"-",""),"00-00")

--
Regards
Frank Kabel
Frankfurt, Germany

"hkslater" schrieb im Newsbeitrag
...
I have a data field that I want to instert a leading zero (where
necessary)
so that I may sort the data correctly. Is there an easy way to do
this?

My service codes currently sort as follows:

32-1
32-10
32-11
32-12
32-2
32-20
32-21
32-3

Is there formula that will insert a leading zero into the 32-1, 32-2,
32-3
etc. to make it 32-01, 32-02, 32-03?

Thanks for the help.

Assuming no more than 2 digits to the right of the dash, try:

=LEFT(A1,FIND("-",A1))&TEXT(RIGHT(A1,2),"00;00")

Tim C

"hkslater" wrote:

I have a data field that I want to instert a leading zero (where necessary)
so that I may sort the data correctly. Is there an easy way to do this?

My service codes currently sort as follows:

32-1
32-10
32-11
32-12
32-2
32-20
32-21
32-3

Is there formula that will insert a leading zero into the 32-1, 32-2, 32-3
etc. to make it 32-01, 32-02, 32-03?

Thanks for the help.

Assuming that their are only 2 characters left of "-"
=IF(LEN(MID(A1,3,1024))=2,SUBSTITUTE(A1,"-","-0"),A1)


"hkslater" wrote in message
...
I have a data field that I want to instert a leading zero (where
necessary)
so that I may sort the data correctly. Is there an easy way to do this?

My service codes currently sort as follows:

32-1
32-10
32-11
32-12
32-2
32-20
32-21
32-3

Is there formula that will insert a leading zero into the 32-1, 32-2, 32-3
etc. to make it 32-01, 32-02, 32-03?

Thanks for the help.

This worked thank you.

"Tim C" wrote:

Assuming no more than 2 digits to the right of the dash, try:

=LEFT(A1,FIND("-",A1))&TEXT(RIGHT(A1,2),"00;00")

Tim C

"hkslater" wrote:

I have a data field that I want to instert a leading zero (where necessary)
so that I may sort the data correctly. Is there an easy way to do this?

My service codes currently sort as follows:

32-1
32-10
32-11
32-12
32-2
32-20
32-21
32-3

Is there formula that will insert a leading zero into the 32-1, 32-2, 32-3
etc. to make it 32-01, 32-02, 32-03?

Thanks for the help.

This actually got me 03-01 instead of 31-01.

"Frank Kabel" wrote:

Hi
or another alternative (a little bit shorter):
=TEXT(--SUBSTITUTE(A1,"-",""),"00-00")

--
Regards
Frank Kabel
Frankfurt, Germany

"hkslater" schrieb im Newsbeitrag
...
I have a data field that I want to instert a leading zero (where
necessary)
so that I may sort the data correctly. Is there an easy way to do
this?

My service codes currently sort as follows:

32-1
32-10
32-11
32-12
32-2
32-20
32-21
32-3

Is there formula that will insert a leading zero into the 32-1, 32-2,
32-3
etc. to make it 32-01, 32-02, 32-03?

Thanks for the help.

Hi, Frank:

You are staying up too late <bg!

Your first formula was missing a left paren before the first &. After that
fix, it gave me two dashes in the results, which was fixed by inserting +1
before the ",10" in the MID formula. So it ended up as this, which worked
correctly.

=LEFT(A1,FIND("-",A1)) & TEXT(--(MID(A1,FIND("-",A1)+1,10)),"00")

For the 2nd, shorter formula, no cigar on that one <bg. It inserts the
additional zero at the front instead of after the dash.

32-1 comes out as 03-21 instead of 32-01

Myrna Larson


On Tue, 16 Nov 2004 22:42:19 +0100, "Frank Kabel"
wrote:

Hi
or another alternative (a little bit shorter):
=TEXT(--SUBSTITUTE(A1,"-",""),"00-00")