Remove leading zero within a text field

[bflanagan]

Hello,

I have a list of codes I need to removed the leading zero from. However, I only want to remove the leading zero and not all of them.

I have been looking for a while but cant seem to figure it out. Any ideas?

The codes look like this:

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]AB01[/TD]
[/TR]
[TR]
[TD="class: xl65"]AB02[/TD]
[/TR]
[TR]
[TD="class: xl65"]AB03[/TD]
[/TR]
[TR]
[TD="class: xl65"]AB05[/TD]
[/TR]
[TR]
[TD="class: xl65"]AB10
[/TD]
[/TR]
</tbody>[/TABLE]
and I need them to look like this:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]AB1[/TD]
[/TR]
[TR]
[TD]AB2[/TD]
[/TR]
[TR]
[TD]AB3[/TD]
[/TR]
[TR]
[TD]AB5[/TD]
[/TR]
[TR]
[TD]AB10


Thanks![/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

It would have helped to get all those variations up front in the original post..

Try
=SUBSTITUTE(LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&15^12))-1),0,"")&RIGHT(A1,LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&15^12))+1)

 

[bflanagan]

Awesome - works like a treat! Thanks for your help!

how about

Excel 2012

	A	B
AB01	AB1
AB02	AB2
AB03	AB3
AB05	AB5
AB10	AB10
AB11	AB11
AB12	AB12
AB13	AB13
AB14	AB14
AB15	AB15

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=IFERROR(IF(FIND(0,A1)<LEN(A1),SUBSTITUTE(A1,0,""),A1),A1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[bflanagan]

Hi Jonmo1,

That worked perfectly - thank you!

Apologies, I only noticed a few had letters after the numbers when I ran your initial formula.

Thanks agin for your help!

It would have helped to get all those variations up front in the original post..

Try
=SUBSTITUTE(LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&15^12))-1),0,"")&RIGHT(A1,LEN(A1)-MIN(FIND({1,2,3,4,5,6,7,8,9},A1&15^12))+1)

 

[Jonmo1]

You're welcome.

 

[vicedo]

you could have as well select the entire column and hit ctrl h. Find what = 0 Replace with= leave blank

 

[Jonmo1]

you could have as well select the entire column and hit ctrl h. Find what = 0 Replace with= leave blank

That would change AB10 to AB1

 

[bosco_yip]

There are also sometimes 3 characters before yes.

Here are the different types of codes:


A01
AB01
ABC01
A01B

Also, some codes are already in the correct format e.g.

A1
AB1
ABC1
A1B

Another option :

=IFERROR(REPLACE(A1,FIND(0,A1&0),MIN(FIND({1,2,3,4,5,6,7,8,9},A1&1/17))-FIND(0,A1&0),""),A1)

 

[István Hirsch]

Awesome - works like a treat! Thanks for your help!

If this formula finds a „0” anywhere in the string except the end position, deletes all zeros, for example, AB010 becomes AB1, AB202 becomes AB22. Is this what you need?

 

[Rick Rothstein]

This formula appears to work correctly for all the possible variations mentioned so far (including A202 and A010 and their variations)...

=REPLACE(A1,FIND(0,A1&0),ISERR(MID(A1,FIND(0,A1&0)-1,1)+0),"")

 

[Rick Rothstein]

This formula appears to work correctly for all the possible variations mentioned so far (including A202 and A010 and their variations)...

=REPLACE(A1,FIND(0,A1&0),ISERR(MID(A1,FIND(0,A1&0)-1,1)+0),"")

Of course, it will not remove all leading zeroes if there are more than one of them (AB001 for example becomes AB01 with my formula)... we need the OP to tell us if that is a possibility or not.