Page 1 of 2 12 LastLast
Results 1 to 10 of 20

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. #1
    New Member
    Join Date
    Dec 2015
    Posts
    10

    Post 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. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    42,171

    Default 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
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    2,175

    Default 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. #4
    New Member
    Join Date
    Dec 2015
    Posts
    10

    Default 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. #5
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    42,171

    Default 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?
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  6. #6
    New Member
    Join Date
    Dec 2015
    Posts
    10

    Default 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. #7
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    42,171

    Default 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
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  8. #8
    New Member
    Join Date
    Dec 2015
    Posts
    10

    Default 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. #9
    New Member
    Join Date
    Dec 2015
    Posts
    10

    Default 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. #10
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    2,175

    Default Re: Remove leading zero within a text field

    Quote Originally Posted by bflanagan View Post
    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!

    how about

    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 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com