Excel Fox
10-17-2011, 06:20 AM
And to illustrate how this code works
Consider the case fdsff43t59r178dq, where the output expected is 4359178
1. ROW(INDIRECT("1:"&LEN(A1))) is equivalent to ROW(INDIRECT("1:"&16)) which is equivalent to ROW(INDIRECT("1:16"))
When the above is used in an array formula, which also includes a SUMPRODUCT function, it acts like an array of values from 1 to 16, ie
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}
2. MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) is equivalent to MID("fdsff43t59r178dq",{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},1) which results in the array {"f", "d", "s", "f", "f", "4", "3", "t", "5", "9", "r", "1", "7", "8", "d", "q"}
3. From the above, ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1) would equate to ISNUMBER({"f", "d", "s", "f", "f", "4", "3", "t", "5", "9", "r", "1", "7", "8", "d", "q"}/1).
If you use any mathematical operation on a text that is numeric in nature, it will return another numeric value, and the other would return an error. So the above formula would essentially give an array {FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE}
4. Now comes LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))
For this you need to understand how the LARGE function works (I'll skip that).
The above equation would equate to
LARGE({FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE}
*{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},{1,2,3,4 ,5,6,7,8,9,10,11,12,13,14,15,16}) which would then result as
LARGE({0, 0, 0, 0, 0, 6, 7, 0, 9, 10, 0, 12, 13, 14, 0, 0},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}) which would then result as
{14,13,12,10,9,7,6,0,0,0,0,0,0,0,0,0}
5. Now comes the MID formula
MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)
You all know how the MID formula works. MID(TextToLookAt,CharacterPositionToStartFrom,Leng thOfCharacters)
The character position to start from should not be less than 1. Our array above, ie, {14,13,12,10,9,7,6,0,0,0,0,0,0,0,0,0} has quite a few zeroes in it. To nullify a probable error in the formula, we add a 0 (zero) to the beginning of the original text, and we offset the array above by 1 (one). There's another reason why we add a zero to the beginning, which will be clear as we continue with the explanation.
So the MID formula would equate to
MID("0fdsff43t59r178dq", {15,14,13,11,10,8,7,1,1,1,1,1,1,1,1,1}, 1) would equate to {"8", "7", "1", "9", "5", "3", "4", "0", "0", "0", "0", "0", "0", "0", "0", "0"}
6. Now comes the really interesting part of the equation.
You know what x^y, ie, X powered by Y, means, so this should be straight forwards
MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1) would equate to
{"8", "7", "1", "9", "5", "3", "4", "0", "0", "0", "0", "0", "0", "0", "0", "0"}^{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}-1 which equates to
{"8", "7", "1", "9", "5", "3", "4", "0", "0", "0", "0", "0", "0", "0", "0", "0"}^{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15} which equates to
{8, 70, 100, 9000, 50000, 300000, 4000000, 0, 0, 0, 0, 0, 0, 0, 0, 0}
7. SUMPRODUCT({8, 70, 100, 9000, 50000, 300000, 4000000, 0, 0, 0, 0, 0, 0, 0, 0, 0}) would give us the result 4359178
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.