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:

AB01

AB02

AB03

AB05

AB10

<tbody>
</tbody>

and I need them to look like this:

AB1

AB2

AB3

AB5

AB10 Thanks!

<tbody>
</tbody>

 

[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
1	AB01	AB1
2	AB02	AB2
3	AB03	AB3
4	AB05	AB5
5	AB10	AB10
6	AB11	AB11
7	AB12	AB12
8	AB13	AB13
9	AB14	AB14
10	AB15	AB15

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B1 =IFERROR(IF(FIND(0,A1)<LEN(A1),SUBSTITUTE(A1,0,""),A1),A1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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.