- Thread starter jeffreyweir
- Start date

Still waiting for your submission, Hui ;-)

Pure genius.

This bit in bold:

=MID(A16,MATCH(1,MMULT(-ISERR(-MID(MID(A16,

…evaluates to this:

=MID(A16,MATCH(1,MMULT(-ISERR(-MID(MID(A16,

This bit in bold:

=MID(A16,MATCH(1,MMULT(-ISERR(-MID(

…evaluates to this:

=MID(A16,MATCH(1,MMULT(-ISERR(-MID(

This bit in bold:

=MID(A16,MATCH(1,MMULT(-ISERR(-

…evaluates to this:

=MID(A16,MATCH(1,MMULT(-ISERR(-

This bit in bold:

=MID(A16,MATCH(1,MMULT(-ISERR(

…evaluates to this:

=MID(A16,MATCH(1,MMULT(-ISERR(

This bit in bold:

=MID(A16,MATCH(1,MMULT(-ISERR({#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,-7;-7,-1;-1,-9;-9,0;0,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,-1;-1,-4;-4,-8;-8,-4;-4,#VALUE!}

…evaluates to this:

=MID(A16,MATCH(1,MMULT(-{TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE}

This bit in bold:

=MID(A16,MATCH(1,MMULT(-{TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE}

…evaluates to this:

=MID(A16,MATCH(1,MMULT({-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,0;0,0;0,0;0,0;0,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,0;0,0;0,0;0,0;0,-1}

This bit in bold:

=MID(A16,MATCH(1,MMULT({-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,0;0,0;0,0;0,0;0,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,-1;-1,0;0,0;0,0;0,0;0,-1}

…evaluates to this:

=MID(A16,MATCH(1,{0;0;0;0;0;-1;0;0;0;1;0;0;0;0;0;0;0;0;-1;0;0;0;1}

This bit in bold:

=MID(A16, MATCH(1,{0;0;0;0;0;-1;0;0;0;1;0;0;0;0;0;0;0;0;-1;0;0;0;1},0)+1

…evaluates to this:

=MID(A16, 11

If it's changed to

Code:

`=MID(A4,2+MATCH(1,(CODE(MID(A4,ROW(A$1:INDEX(A:A,LEN(A4))),1))<58)*(CODE(MID(A4&"a",ROW(A$3:INDEX(A:A,LEN(A4)+1)),1))>57),0),LEN(A4))`

I get what's expected.

The problem is with the second ROW/INDEX combo. Should have been this:

=MID(A1,1+MATCH(1,(CODE(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1))<58)*(CODE(MID(A1&”a”,ROW(A$

=MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$32767),1))>57)*(ROW($1:$32767)>MIN(FIND(ROW($1:$10)-1,A1&57321^2))),0),32767)

That

3285697041

...which is a pandigital number i.e. it contains every number from 0 to 9. Meaning you will never get an error when searching the string for the numbers 0 to 9.

Elias' formula can be made shorter still by using whole-column references within the ROW argument:

' =MID(A1,MATCH(1,(CODE(MID(A1,ROW(A:A),1))>57)*(ROW(A:A)>MIN(FIND(ROW(A:A)-1,A1&57321^2))),0),8^5)'

...But this makes it very very resource intensive. It could go shorter still if he can find a shorter way to write a similar digit to 57321^2 that includes all digits from 0 to 9. i.e some combo of x^y that contains all digits from 0 to 9

7^18

...which means we can tighten that puppy up even more:

=MID(A1,MATCH(1,(CODE(MID(A1,ROW(A:A),1))>57)*(ROW(A:A)>MIN(FIND(ROW($1:$10)-1,A1&7^18))),0),9^9)

97 characters. Ouch!

89 characters

But now that I have read your summary post in Chandoo, I see I've used exactly the same logic as your original idea, i.e. find the first letter preceded by a number.

Still clever maybe, but beaten to the punch.

If we replace your whole-row references with something a little less computationally intensive (not that we have to, mind), then we get this:

=MID(A1,MATCH(1,(CODE(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1))>57)*(CODE(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1)))-1,1))<58),0),LEN(A1))

...which is 127 characters as opposed to my original 133. So your tweak on my approach beat me fair and square!

(Don't worry Sajan...I cover your even shorter formula off in a future post)

=MID(A1,MAX(IFERROR(FIND((ROW($1:10)-1)&CHAR(COLUMN(BM:CL)),UPPER(A1)),0))+1,LEN(A1))

...which can be shortened even further because the UPPER isn't required. So that makes it 79 characters.

There you go. Really spoiled my punch-line because at the end of this series (a couple of posts of which are still to come) I was going to challenge readers to find a shorter solution than the respondents had so far posted, with a slightly longer version of his in mind that I'd come up with when writing these posts up. Specifically =MID(A1,MIN(IFERROR(FIND(CHAR(COLUMN($AW:$BF))&CHAR(ROW($65:$90)),A1),”"))+1,LEN(A1))

Great skills, David.

Haseeb, you're now officially an Excel rock-star. Can I have your formula-graph? ;-)

=MID(A4,MATCH(TRUE,(1+MID(A4,ROW(A:A),1))*ISERR( -MID(A4,ROW(A:A)+1,1))>0,)+1,9^9)

If you're interested in walking through Elias' formula, you might want to use this longer version that doesn't use whole row references, because a) it will calculate faster and b) you will be able to evaluate it bit by bit using either F9 or the Evaluate Formula functionality:

=MID(A1,MATCH(TRUE,(1+MID(A1,ROW(OFFSET(A$1,,,LEN(A1))),1))*ISERR( -MID(A1,ROW(OFFSET(A$1,,,LEN(A1)))+1,1))>0,)+1,9^9)

Interesting. Very clever how he checks for

1+MID(A1,ROW(OFFSET(A$1,,,LEN(A1))),1)

...and then checks for

ISERR( -MID(A1,ROW(OFFSET(A$1,,,LEN(A1)))+1,1))

...and then multiplies these arrays together, to find the first position where there is a number:

MATCH(TRUE,{array}>0,)

Genius!

I never knew until just now when I checked out Elias' formula that if the third argument of MATCH is a comma followed by nothing else, Excel interprets this the same as if that third argument was FALSE or Zero. Guess that makes sense...INDEX works the same way. But I never new MATCH did. So that's a handy way to shorten a formula if you're answering a challenge (although I'd leave it in in a real-world situation).

So with a blank comma as the 3rd agrument, all these are equivalent:

=MATCH(3,{1,2,4,5},)

=MATCH(3,{1,2,4,5},FALSE)

=MATCH(3,{1,2,4,5},0)

= #N/A

And without

=MATCH(3,{1,2,4,5})

= MATCH(3,{1,2,4,5},TRUE)

= MATCH(3,{1,2,4,5},1)

= 2

Also, it's interesting to note that MATCH can handle errors, so Elias doesn't have to add more code to strip them out.

Damn, have already written and scheduled post for monday on your second effort and might not have time to revise it.

I think we are all enjoying the write up... so to ensure you have enough material, here is another shorter array formula version:

=MID(A1,MATCH(1,-LEFT(TEXT(MID(A1,ROW(A:A),2),"A"),1))+1,6^6)

I had to give up my MMULT function, but got it to 61 characters.

(For testing, I would suggest replacing the A:A with something like A1:A50.)

Cheers,

Sajan.

=MID(A1,MATCH(1,-LEFT(TEXT(MID(A1,ROW(OFFSET(A$1,,,LEN(A1))),2),"A"),1))+1,6^6)

...which is as short as David Hagar's one:

=MID(A1,MAX(IFERROR(FIND((ROW($1:$10)-1)&CHAR(COLUMN(BN:CM)),A1),0))+1,LEN(A1))