# Remove leading zero within a text field

This is a discussion on Remove leading zero within a text field within the Excel Questions forums, part of the Question Forums category; Hello, I have a list of codes I need to removed the leading zero from. However, I only want to ...

1. ## Remove leading zero within a text field

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
and I need them to look like this:

 AB1 AB2 AB3 AB5 AB10 Thanks!

2. ## Re: Remove leading zero within a text field

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

3. ## Re: Remove leading zero within a text field

try if this little trick works for you or not

Excel 2012
ABC
1AB01AB1
2AB02AB2
3AB03AB3
4AB05AB5
5AB10AB10

Sheet2

Worksheet Formulas
CellFormula
C1=IF(FIND(0,A1)<LEN(A1),SUBSTITUTE(A1,0,""),A1)

4. ## Re: Remove leading zero within a text field

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

5. ## Re: Remove leading zero within a text field

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

6. ## Re: Remove leading zero within a text field

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

7. ## Re: Remove leading zero within a text field

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

8. ## Re: Remove leading zero within a text field

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!

9. ## Re: Remove leading zero within a text field

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!!

10. ## Re: Remove leading zero within a text field

Originally Posted by 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!

Excel 2012
AB
1AB01AB1
2AB02AB2
3AB03AB3
4AB05AB5
5AB10AB10
6AB11AB11
7AB12AB12
8AB13AB13
9AB14AB14
10AB15AB15

Sheet2

Worksheet Formulas
CellFormula
B1=IFERROR(IF(FIND(0,A1)<LEN(A1),SUBSTITUTE(A1,0,""),A1),A1)

Page 1 of 2 12 Last