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 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.
42 Responses
Hi Chandoo,
This fails when it starts with any charcter after the Zeros… like 00FG345L. Any solution for this ?
@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))@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
Thanks Deb. This is the way I prefer to do this kind of thing. Sweet function. 🙂
Would you not be better served with:
RemoveLeadingZeros = Mid(RemoveLeadingZeros, 2)
being replaced by
RemoveLeadingZeros = Right(RemoveLeadingZeros, len(RemoveLeadingZeros)-1)
Or am I missing something?
@Deb,
The VBA function can be done without using a loop…
<code>
Function RemoveLeadingZeros(S As String) As String
RemoveLeadingZeros = Replace(LTrim(Replace(S, “0”, ” “)), ” “, “0”)
End Function
</code>
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))
It would be nice if we had some forensics on this one. Thanks.
@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
Hi Vishwa
Try
=MID(A1,SUM(IFERROR(FIND(REPT(CHAR(48),ROW(INDIRECT(“1:”&LEN(A1)))),A1),0))+1,256)
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”))
To get 001234 to 1234 all you have to do is =TEXT(VALUE(001234),”0000″)
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)
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)
=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.
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))
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,””)
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,””)
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!
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.
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!
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”)
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.
jeremy.. this can only possible if “0” will lead after a number. All values that have letters after zero’s cannot be cleaned.
Assuming there are no spaces in the data (as shown), you can use this simple formula…
=SUBSTITUTE(TRIM(SUBSTITUTE(A1,0,” “)),” “,0)
Never mind… that formula will not work correctly if the text ends with zeroes.
This will remove all the zero’s. not just in the beginning but also at the mid and end
I guess this works in excel 2003 only…
@Sanika
No, The formulas don’t use any 2007, 2010 or 2013 specific functions
Which formula is specifically not working ?
The substitue formula is not working..
@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
@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.
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!
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 😉
@Ajit
Except if you try that on 00010230100R you get 1231R rather than 10340100R……
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.
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))
=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
Thanks so much for your website…Downloaded your interaction file. Brilliant.
=value()
Please ignore what I just said. I didn’t read the question clearly.
A different solution – this works with any leading character. Have used the concept from Formula Forensic No. 021 – Find the 4th Slash !
Here B1 represents the leading character to be replaced.
=MID(A1,MATCH(0,(VALUE(MID(A1,ROW(INDIRECT(“1:200”)),1))=$B$1)*ROW(INDIRECT(“1:200”)),0),LEN(A1))