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]

Welcome to the board.

Try this assuming the first part is always 2 characters before the numbers.

=LEFT(A1,2)&RIGHT(A1,LEN(A1)-2)+0

 

[AlanY]

try if this little trick works for you or not

Excel 2012
	A	B	C
1	AB01		AB1
2	AB02		AB2
3	AB03		AB3
4	AB05		AB5
5	AB10		AB10
Sheet2

Cell Formulas
Range	Formula
C1	=IF(FIND(0,A1)A1),SUBSTITUTE(A1,0,""),A1)

 

[bflanagan]

Thanks Jonmo1, that worked really well!

Only problem is a few of my codes only have 1 character before the leading zero. Is there a way to take this into account?

Thanks

 

[Jonmo1]

Are there any other variations?
Is it always either 1 or 2 characters before the first number, never 3 or more?

 

[bflanagan]

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

 

[Jonmo1]

Try

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

 

[bflanagan]

Thanks AlanY!

This worked really well! However any data that was already correct (i.e. without the leading zero) fails.

E.g.

[TABLE="width: 168"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]AB01[/TD]
[TD]AB1[/TD]
[/TR]
[TR]
[TD]AB02[/TD]
[TD]AB2[/TD]
[/TR]
[TR]
[TD]AB03[/TD]
[TD]AB3[/TD]
[/TR]
[TR]
[TD]AB05[/TD]
[TD]AB5[/TD]
[/TR]
[TR]
[TD]AB10[/TD]
[TD]AB10[/TD]
[/TR]
[TR]
[TD]AB11[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]AB12[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]AB13[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]AB14[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]AB15[/TD]
[TD="align: center"]#VALUE!


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

 

[bflanagan]

Works really well! Thanks.

It fails on codes where there is a letter after the code (i.e. AB1C), however as there aren't many of these I can just manually update them. Thanks!!

 

[AlanY]

Thanks AlanY!

This worked really well! However any data that was already correct (i.e. without the leading zero) fails.

E.g.

[TABLE="width: 168"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]AB01[/TD]
[TD]AB1[/TD]
[/TR]
[TR]
[TD]AB02[/TD]
[TD]AB2[/TD]
[/TR]
[TR]
[TD]AB03[/TD]
[TD]AB3[/TD]
[/TR]
[TR]
[TD]AB05[/TD]
[TD]AB5[/TD]
[/TR]
[TR]
[TD]AB10[/TD]
[TD]AB10[/TD]
[/TR]
[TR]
[TD]AB11[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]AB12[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]AB13[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]AB14[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]AB15[/TD]
[TD="align: center"]#VALUE!


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

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
Sheet2

Cell Formulas
Range	Formula
B1	=IFERROR(IF(FIND(0,A1)A1),SUBSTITUTE(A1,0,""),A1),A1)