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.
0 comments:
Post a Comment