Let's step through the Evaluate process together:
In my example, I have the value abf5fb6
in cell A2
, which evaluates to 56
.
First step, replace A2
with value at cell A2
Before:
=SUM(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
After:
=SUM(MID(0&"abf5fb6",LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
Notice how the text now in enclosed inside a pair of quotation marks. This means that it is text, otherwise known as a string.
Now is a good time to briefly explain the MID
function. This function will simply extract some text from a string. The first argument is the text, or string, that we will start with. The second argument is the start position of where we want to start extracting. The third argument is the number of characters we want to extract for, or the length of our ending result. For example, =MID("wizlog", 1, 3
will return wiz
while =MID("wizlog", 2, 5)
will return izlog
So, the next step then concatenates the 0&"abf5fb6"
, because as noted above, the first argument in the MID
function requires text (again, called a string). But in order to add a number to a string in Excel, you use the &
symbol.
Before:
=SUM(MID(0&"abf5fb6",LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
After:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
Now, we will jump to the next MID
function to replace the other A2
with it's value again, just like before.
After:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID("abf5fb6,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
Next, we're dealing with the second argument in the second MID
function, ROW($1:$99)
. Remember, the second argument in the MID
function just gives us the starting position. The ROW
function on the other hand simply just returns the row it is given, so since we pass it the range of 1-99, then it will return to us an array, or a list, of 1-99. This means that we plan to use the MID
function 99 times, each time starting at a different position from 1 to 99.
Before:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID("abf5fb6",ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
After:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID("abf5fb6",{1;2;3;...;99},1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
(To save room, I didn't type out all of the numbers from 1-99, but I'm sure you get the point.)
Now that we have all the pieces to the second MID
function, we can calculate this part out.
Before:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--MID("abf5fb6",{1;2;3;...;99},1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
After:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--{"a";"b";"f";"5";"f";"b";"6";"";"";"";...;""})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
So what just happened here? Well the MID
function just returns a substring of the string given, starting at the given location, for the specified number of characters. So we gave it the string, abf5fb6
, gave it an array of starting positions, specifying we only want to extract 1 character. Thus, the function returns to us an array of each character in our string, from 1-99. Since our starting string was only 7 characters long, then the positions 8-99 are just empty, thus why we have all of the empty substrings after, ("";"";"";...;"";
).
The next function to calculate is the ISNUMBER
function, but first let's go over an odd feature we do first. Notice how there is a double minus in front our new array. While a single minus sign will reverse the result of an outcome (if TRUE
then return FALSE
and visa-vera), but a double minus means to force the string answer into a number. So usually this will result in the answers of TRUE
to turn into a 1
and a FALSE
to turn into a 0
, but in this case, we are converting each character in our array to be a number. So entering in --"a"
will result in #VALUE!
while entering in --"5"
will result in 5
.
Thus, when we execute the ISNUMBER
function:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER(--{"a"; "b"; "f"; "5"; "f"; "b"; "6"; ""; ""; ""; ...; ""})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
What we are actually executing behind the scenes is:
=SUM(MID("0abf5fb6",LARGE(INDEX(ISNUMBER({#VALUE!; #VALUE!; #VALUE!; 5; #VALUE!; #VALUE!; 6; #VALUE!; #VALUE!; #VALUE!; ...; #VALUE!})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
As a result, it turns an array of TRUE
or FALSE
indicating if the value was a number or not. Hence we get:
=SUM(MID("0abf5fb6",LARGE(INDEX({FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; ...; FALSE})*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
Ran out of time
Well I'm at work doing what I'm not supposed to be doing, and this was as far as I got before I need to actually do something productive today. Hopefully I'll pick back up where I left off later on.