Formula Forensics No. 027 – Remove Leading Zeroes

Posted on August 23rd, 2012 in Formula Forensics , Huis , Posts by Hui - 41 comments

A few weeks ago Chandoo received an email from a reader, Chandu:

“I am in search of a formula for the below scenario, please suggest:

 I am trying to delete the zeros before the numbers.

Eg:

002459J

0002459R

02459O

I need one unique formula in case of huge data, please suggest.”

 

Chandoo responded with two solutions:

If you want to get rid of all 0’s:

=Substitute(B2, “0”, “”)

If you want to get rid of all leading 0’s:

=Mid(B2, Min(Iferror(Find({1,2,3,4,5,6,7,8,9}, B2), “”)), LEN(B2)) CTRL+Shift+Enter

 

So today we will look at these two formulas and see what makes them tick.

As always at Formula Forensics you can follow along using a Worked Example which you can download here: Excel 97-2013.

 

Substitute(… )

Chandoo’s first formula uses the Excel Substitute() function to replace all the 0’s with a Null character. The Null character is what is between the two quotation marks “” (Nothing).

The syntax of the Excel Substitute Function is:

In Chandoo’s formula =Substitute(B2,”0″,””) we see that

Text: = B2

Old_Text: “0”

New Text: “” Null Character

So this formula says: Replace all the 0 characters in the text in Cell B2 with “”

 

Advantages: Simple formula if the text only has leading zeroes eg: 002459J will correctly return 2459J

Disadvantages: Doesn’t work if the text has internal or trailing zeroes eg: 0024509J will incorrectly return 2459J

 

Mid( … )

Chandoo’s second formula was the array formula:

=MID(B2,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9},B2),””)),LEN(B2)) Ctrl+Shift+Enter

This formula is based on the Excel Mid() function which returns a Sub-String from within the source String

The string is the text that starts at the position of the first non zero number in the string

 

Let’s start in the middle and work our way out to see what is happening here:

=MID(B2,MIN(IFERROR(FIND({1, 2, 3, 4, 5, 6, 7, 8, 9}, B2),””)),LEN(B2))

In the middle of the formula we see the Excel Find() function.

The syntax of Find() is:

In Chandoo’s Formula

Find_text: {1,2,3,4,5,6,7,8,9,}

Within_text: B2

Start_num: 1 (Default)

 

So the Find() function will look for the values 1 to 9 in the cell B2

Lets see that in an example

In C26 put one of the values 0024059J

In a blank cell D28 put =FIND({1, 2, 3, 4, 5, 6, 7, 8, 9}, C26) then press F9, not Enter

Excel responds with: ={#VALUE!,3,#VALUE!,4,6,#VALUE!,#VALUE!,#VALUE!,7}

Looking at this we can see that the formula has returned 4 values of 3, 4 6 & 7 with some #VALUE! Errors in between.

The values 3, 4, 6 & 7 are the positions in cell C26 of the values 1, 2, 3, 4, 5, 6, 7, 8 & 9

We can see that C26 contains: 0024059J and that in positions 3, 4, 6 & 7 we have values from the array 1, 2, 3, 4, 5, 6, 7, 8 & 9

 

Stepping out of the original formula a little bit =MID(B2,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9},B2),””)),LEN(B2))

We can see that the above Find() formula is surrounded by an Iferror() function.

This will take the results of the Find() function and where there is an error insert a “”

 

In a blank cell D30 put =IFERROR(FIND({1,2,3,4,5,6,7,8,9},C26),””) then press F9, not Enter

Excel responds with: ={“”,3,””,4,6,””,””,””,7}

The #VALUE! Errors have been converted to “”

 

Stepping out a bit more in our original formula we encounter a Min() function next.

=MID(B2,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9},B2),””)),LEN(B2))

 

The Min() function will return the Minimum value from the Iferror() function

So Min(IFERROR(FIND({1,2,3,4,5,6,7,8,9},B2),””)) is the same as Min({“”,3,””,4,6,””,””,””,7})

Which we can see is 3

But lets check that:

In a blank cell D32 type: =MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9},C26),””)) then press F9, not Enter

Excel responds with: 3 as we deduced above,

 

Finally we arrive at our original formula: =MID(B2,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9},B2),””)),LEN(B2))

Which can now be simplified to

=MID(0024059J, 3, LEN(0024059J))

The length of 0024059J is 8 characters long and so the formula becomes

=MID(0024059J, 3, 8)

What this is asking is return the Middle 8 characters of the text 00245059J starting at position 3

Which is 24059J

This has effectively stripped of the left or leading zeroes as Chandu required.

 

Download

You can download a copy of the above file and follow along, Download Here – Excel 97-2013.

 

Formula Forensics “The Series”

This is the 27th post in the Formula Forensics series.

You can learn more about how to pull Excel Formulas apart in the following posts

Formula Forensic Series

 

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.

If you have a neat formula that you would like to share, try putting pen to paper and draft up a Post like above or;

If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.

 

 

 

Written by Hui...
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

41 Responses to “Formula Forensics No. 027 – Remove Leading Zeroes”

  1. Vishwa says:

    Hi Chandoo,

    This fails when it starts with any charcter after the Zeros... like 00FG345L. Any solution for this ?

    • Hui... says:

      @Vishwa
      It only fails when the first non 0 character isn't a number
      However, the following formula fixes that:
      =MID(C26, MIN(IF(CODE(MID(C26, ROW(OFFSET($A$1,,,LEN(C26))),1))>48, ROW(OFFSET($A$1,,,LEN(C26))))), LEN(C26))

    • Debraj Roy says:

      @Vishwa,
      If you can afford a UDF, then you may go for below function..
      Excel >> VBA (Alt + F11) >> Insert New Module >> Paste the below code
      `
      Function RemoveLeadingZeros(strInput)
        RemoveLeadingZeros = strInput
        Do While Left(RemoveLeadingZeros, 1) = "0"
          RemoveLeadingZeros = Mid(RemoveLeadingZeros, 2)
        Loop
      End Function
      `
      and in Excel write Formula as
      `=RemoveLeadingZeros(A2)`
      Regards,
      Deb

    • Vishwa says:

      Thanks all.... meanwhile i tried one more method to solve this issue

      =MID(B2,MATCH(TRUE,(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)<>"0"),0),LEN(B2))

      • Novicetech1 says:

        It would be nice if we had some forensics on this one. Thanks.

        • Hui... says:

          @Novicetech1
          Why don't you try using some of the techniques I discuss in Formula Forensics to look at he formula
          Start in the middle and select an entire function, evaluate it with F9
          Work out what it's doing and why
          Then step out a function 

    • BruceW says:

      Hi Vishwa

      Try
      =MID(A1,SUM(IFERROR(FIND(REPT(CHAR(48),ROW(INDIRECT("1:"&LEN(A1)))),A1),0))+1,256)

      • Jeanbar says:

        Vishwa,
        Nice formula to find out the first "non-leading zero" character.
        BruceW,
        For those wo can't use the IFERROR() function (Yes I'am using the 2003 version :()
        =MID(B2,SUM(N(MID(B2,1,LEN(T_00))=T_00)),256) + CSE
        where T_00 is defined as =REPT("0",ROW(INDIRECT("1:10"))

  2. Kevin says:

    To get 001234 to 1234 all you have to do is =TEXT(VALUE(001234),"0000")

  3. Rosco says:

    This one seems to cover all bases and doesn't require Ctrl+Shift+Enter

     =RIGHT(B2,LEN(B2)-MATCH(TRUE,MID(B2,{1,2,3,4,5,6,7,8,9},1)<>"0",0)+1) 

  4. James Blair says:

    If you modify the substitut formula into a RIGHT formula then you can do it for any character after the 0s and not have to use an array:

    assuming the target cell is C11

    =RIGHT(C11,LEN(C11)-SEARCH(LEFT(SUBSTITUTE(C11,"0",""),1),C11)+1)

  5. Stephen says:

    =RIGHT(A1,LEN(A1)-IF(LEFT(A1)="0",IF(MID(A1,1,1)="0",IF(MID(A1,2,1)="0",IF(MID(A1,3,1)="0",IF(MID(A1,4,1)="0",IF(MID(A1,5,1)="0",5,4),3),2),1),0),0))

    A little longer, but more intuitive, for me anyway; it will strip up to 5 0's from the beginning.  More if statements could be used if more zeros are present. 

  6. Stephen says:

    Sorry, the line was too long.

    =RIGHT(A1,LEN(A1)-IF(LEFT(A1)="0",IF(MID(A1,1,1)="0", IF(MID(A1,2,1)="0",IF(MID(A1,3,1)="0",IF(MID(A1,4,1)="0", IF(MID(A1,5,1)="0",5,4),3),2),1),0),0))

  7. shrivallabha says:

    Only for the sample data posted.
    =--LEFT(A3,LEN(A3)-1)&RIGHT(A3)
    will also work.
    And following approach should work generally without CSE:
    =REPLACE(A1,1,MATCH(TRUE,MID(A1,{1,2,3,4,5,6,7,8,9},1)<>"0",0)-1,"")

    • shrivallabha says:

      Or to make it handle any number of zeroes (NEED CSE):
      =REPLACE(A1,1,MATCH(TRUE,MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)<>"0",0)-1,"")

  8. Squiggler says:

    Or simpler, not using array formulas:

    =IFERROR(MID(B2,FIND(LEFT(SUBSTITUTE(B2,"0",""),1),B2),65535),"")

    Will handle any number of leading zeros, and doesn't care if the string is numeric or alphabetic or both!
     

    • Squiggler says:

      Just remove all zero's find the first character that isn't zero and search for it in the string, Strip off everything before this character.

      • Squiggler says:

        In-fact, =MID(B2,FIND(LEFT(SUBSTITUTE(B2,"0","")),B2),65535)

        will suffice, I put the error in for the blank string but it's not needed, the only case it is needed is if B2 already contains an error!
         

  9. Leonid K. says:

    My non-array version is:
    =REPLACE(A1,1,FIND(LEFT(TRIM(SUBSTITUTE(A1,"0"," ")),1),A1),"")

    where LEFT(TRIM(SUBSTITUTE(A1,"0"," ")),1) gives us the first non zero character,
    FIND(LEFT(TRIM(SUBSTITUTE(A1,"0"," ")),1) finds its position and then REPLACE chops everything to the left up to that position.

    It would be practical to have in Excel a worksheet function LTRIM. Then the formula could be shorter like SUBSTITUTE(LTRIM(SUBSTITUTE(A1,"0"," "))," ","0")

  10. Jeremy says:

    Love the site!  Lot's of awesome content here, and I'm a regular reader. 

    This probably defeats the purpose of the 'Formula Forensics' topic, but a far easier method which requires no formula is to use the 'multiply' functionality from the paste special menu.  Type the number '1' in an empty cell and copy it.  Highlight the cells containing the values with leading zeros and do paste special.  From that menu, select 'values' and then select 'multiply'.  All leading zeros are eliminated and no formula is required!  It's fast too.       

    • harold says:

      jeremy.. this can only possible if "0" will lead after a number. All values that have letters after zero's cannot be cleaned. 

  11. Assuming there are no spaces in the data (as shown), you can use this simple formula...

    =SUBSTITUTE(TRIM(SUBSTITUTE(A1,0," "))," ",0)

  12. Sanika says:

    I guess this works in excel 2003 only...

    • Hui... says:

      @Sanika
      No, The formulas don't use any 2007, 2010 or 2013 specific functions
      Which formula is specifically not working ?

      • Sanika says:

        The substitue formula is not working..

        • Hui says:

          @Sanika
           
          Substitute has been in Excel since day 0 and so it is something wrong at your end
           
          What version of Excel do you use?
          Can you email me the file, click on Hui, email at bottom of page

        • Squiggler says:

          @Sanika, if you cut and pasted the formula watch out for the quotation marks, you may need to overtype them, as the web page uses different ones to the standard ones excel understands. Also some versions of Excel use ; rather than , in the formulas.

      • Jim Watson says:

        Hui,

        The IFERROR function in your formula was not introduced until Excel 2007. Also, in your last paragraph, you inadvertently added an additional "5" in the example text (00245059J). It should be 0024059J. Great article!

  13. Ajit Roy says:

    Hi all
    i think the formula is fine but in this world we all are living in a short cut to do the same job.
     
    go for it
    Ctrl+F ---> type 0 ---> replace with none ----> here you go 🙂 all zero went vanish 😉

  14. George says:

    Since the input is in a string format, wouldn't it make sense if the output was too (if you run it on a number, eg 00001234 you end up with 1234 as a number)?

    I thought CSTR() would work for the VBA solutions, but Excel helpfully converts these strings back to numbers.

    Any help?  Right now I'm using the solution posted by Debraj Roy because I think it's awesome.

  15. Kyle McGhee says:

    Vishwa posted something similar already but here is my take:
    =MID(A1,MATCH(FALSE,INDEX(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=0,),0),LEN(A1))

  16. Faez says:

    =IF(ABS(MID(B2,1,3))=0,ABS(MID(B2,1,4)),ABS(MID(B2,1,3)))&IF(ABS(MID(B2,1,3))=0,(MID(B2,5,LEN(B2))),MID(B2,4,LEN(B2)))

    This formula works with min. 3 digitals before adding letters

    try it

    Thanks
    Faez

              

  17. Olav says:

    Thanks so much for your website...Downloaded your interaction file.  Brilliant.

  18. Ansi says:

    Please ignore what I just said. I didn't read the question clearly.

Leave a Reply