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]

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.

AB01	AB1
AB02	AB2
AB03	AB3
AB05	AB5
AB10	AB10
AB11	#VALUE!
AB12	#VALUE!
AB13	#VALUE!
AB14	#VALUE!
AB15	#VALUE!

<colgroup><col><col></colgroup><tbody>
</tbody>

 

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

AB01	AB1
AB02	AB2
AB03	AB3
AB05	AB5
AB10	AB10
AB11	#VALUE!
AB12	#VALUE!
AB13	#VALUE!
AB14	#VALUE!
AB15	#VALUE!

<colgroup><col><col></colgroup><tbody>
</tbody>

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)