Question on a formula posted in a previous thread

[FDibbins]

Marcol answered a thread with a very useful formula....

http://www.excelforum.com/excel-gene...html?p=2728980

=LOOKUP(99^99,--("0"&MID(C5,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C5&"0 123456789")),ROW($1:$10000))))

I have had need of something like this in the past to seperate an entry like abc123. Could any1 please explain to me how this formula works

[jeffreybrown]

Have you tried "Evaluate Formula" to see the formula as it breaks down through the steps? A great way to disect what is happening.

[FDibbins]

Good suggestion, thx. However, the essence of the process escapes me. I can normally figure out thinfgs that way and have used that method for proofing formulae in the past, but not with this 1

[jeffreybrown]

Well, to be quite honest, not entirely sure myself how this formula works. I understand the Lookup -- Mid -- Min -- Search and so on, but why this formula needs the last part, ROW($1:$10000) not quite sure. Maybe some with some better formula skills will check in on this thread and help. Sorry

[FDibbins]

Im in the same boat as you, Jeff, I can get behind all of the basic stuff (lookup, mid etc), but I cant get my head around the priciple behind the process

[teylyn]

Hello,

the core bit is the Mid function. The ROW($1:$10000) part is used as the third argument of the mid function to extract 1, then 2, then 3, etc. up to 10000 characters into an array of values. The outer Lookup then extracts the largest number from that array.

[daddylonglegs]

This part

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C5&"0123456789"))

finds the position of the first digit in C5. SEARCH generates an array of the positions of the first instance of each digit (and all digits are concatenated to C5 to prevent error), so if C5 contains abc365xyz this array is generated

{10,11,12,4,14,6,5,17,18,19}

MIN obviously finds the minimum of that array, i.e. 4 in my example, which is the position of the first digit

You can use FIND if you want instead of SEARCH....and the quotes aren't really required if you don't have zero at the start so I'd use this variation

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},C5&1234567890))

The MID function then becomes this

=MID(C5,4,ROW($1:$10000))

where the 4 is generated by the first part above

As teylyn says, ROW then generates an array of integers 1 to 10000 which acts as the 3rd argument of the MID function, so MID creates another array, this time of strings starting with character 4 (the first digit) of the string, so in the above that looks like this:

={"3","36","365","365x","365xy","365xyz","365xyz","365xyz","365xyz".....}

Note that once the maximum string length is reached the subsequent strings simply repeat....

Now "0" is concatenated to the front (which has no material effect with my example but which should prevent an error if the string in C5 contains no digits) so you now get this array

{"03","036","0365","0365x","0365xy","0365xyz","0365xyz","0365xyz","0365xyz"......}

and now the -- converts these to numbers or error values so you get this

{3,36,365,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!....}

which is the lookup array for LOOKUP i.e. whole formula now becomes

=LOOKUP(99^99,{3,36,365,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!})

When a large value like 99^99 is used as the lookup value the last numeric value is extracted from the array, i.e. 365 which is the required answer

I would argue that you can't successfully extract any number with more than 15 significant digits with this method (without changing that number) so the ROW part really only has to be ROW($1:$16) to cope with maximum length decimal numbers (although you might want to go beyond 16 if there might be leading zeroes), also if left as just ROW the formula result can be altered by other actions, e.g. insert a row at row 1 in the spreadsheet and that changes to ROW($2:$17), so more robust to use INDIRECT to fix, i.e. ROW(INDIRECT("1:16"))...or simply use the generated array constant, i.e.

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}

so slightly amended version I'd use

=LOOKUP(99^9,(0&MID(C5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C5&1234567890)),ROW(INDIRECT("1:16"))))+0)

[jeffreybrown]

Many thanks DLL, great explanation. This is the part that cleared it up for me...

Note that once the maximum string length is reached the subsequent strings simply repeat....

...but I won't stray too far from this thread if ever asked about this procedure

[FDibbins]

Thanks for a really great explanation DDL, its going to take me a while...and many more reads of this...to get fully behind it

How does the 1st part of the formula work? the =LOOKUP(99^99...part

=LOOKUP(99^99,--("0"&MID(C5,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C5&"0 123456789")),ROW($1:$10000))))

[daddylonglegs]

That's how you get the last number in the array generated by MID.

LOOKUP normally returns the greatest value that is less than or equal to the lookup value in a sorted (ascending) array. So for standard usage with this formula

=LOOKUP(10,{1,8,12,25,100})

the formula returns 8 - the largest value in the ascending array that's still <= to 10

What if you use a "big number" as the lookup value, i.e. a number that you know will be greater than any value in the array, i.e.

=LOOKUP(99^99,{1,8,12,25,100})

of course the greatest value that is still <= 99^99 is the last number 100

but, in fact, if you list the numbers in a different order like this

=LOOKUP(99^99,{100,8,12,25,1})

Excel "expects" the array to be sorted and still returns what should be the greatest value, i.e. the last one - 1 **

In the formula discussed here the array will be something like I suggested in my last post, i.e.

{3,36,365,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!....}

so LOOKUP with lookup value of 99^99 selects the last number from that array (i.e. 365) and ignores the error values.

Of course in this case the last value will normally also be the largest because of the way MID progressively takes more characters

** this is a simplification of a slightly more complex process than I describe here

[FDibbins]

DDL, you are the greatest!! To take the time and make the effort to give such a detailed explanation, is an inspiration. Thank you!