• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 001 - Return everything in string after first block of numbers

On 2nd thought, that approach of finding the first position of the first capital letter starting from the right fails if there is more than one capital letter in the substring, such as RussianFederation. But can probably be fixed with a tweak or three.
 
You are right, it would fail in that case I didn't give any consideration to that scenario occuring. Any tweak would result in more characters!!!


That was a good challenge. I look forward to the next one.


Cheers
 
Hi Haseeb,

Good one!


To give you some motivation to come up with something even shorter, here is a formula that is 102 characters long :)


=MID(A1,MATCH(1,MMULT(-ISERR(-MID(MID(A1,ROW(OFFSET(A$1,,,LEN(A1))),2),{1,2},1)),{1;-1}),0)+1,LEN(A1))


Regards,

Sajan.
 
Stepping through Sajan's latest:


This bit in bold:

=MID(A16,MATCH(1,MMULT(-ISERR(-MID(MID(A16, ROW(OFFSET(A$1,,,LEN(A16))),2),{1,2},1)),{1;-1}),0)+1,LEN(A16))

…evaluates to this:

=MID(A16,MATCH(1,MMULT(-ISERR(-MID(MID(A16, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23},2),{1,2},1)),{1;-1}),0)+1,LEN(A16))


This bit in bold:

=MID(A16,MATCH(1,MMULT(-ISERR(-MID(MID(A16,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23},2),{1,2},1)),{1;-1}),0)+1,LEN(A16))

…evaluates to this:

=MID(A16,MATCH(1,MMULT(-ISERR(-MID({"Mo";"on";"na";"ac";"co";"o7";"71";"19";"90";"0A";"Au";"us";"st";"tr";"ra";"al";"li";"ia";"a1";"14";"48";"84";"4"},{1,2},1)),{1;-1}),0)+1,LEN(A16))


This bit in bold:

=MID(A16,MATCH(1,MMULT(-ISERR(-MID({"Mo";"on";"na";"ac";"co";"o7";"71";"19";"90";"0A";"Au";"us";"st";"tr";"ra";"al";"li";"ia";"a1";"14";"48";"84";"4"},{1,2},1) ),{1;-1}),0)+1,LEN(A16))

…evaluates to this:

=MID(A16,MATCH(1,MMULT(-ISERR(-{"M","o";"o","n";"n","a";"a","c";"c","o";"o","7";"7","1";"1","9";"9","0";"0","A";"A","u";"u","s";"s","t";"t","r";"r","a";"a","l";"l","i";"i","a";"a","1";"1","4";"4","8";"8","4";"4",""}),{1;-1}),0)+1,LEN(A16))


This bit in bold:

=MID(A16,MATCH(1,MMULT(-ISERR(-{"M","o";"o","n";"n","a";"a","c";"c","o";"o","7";"7","1";"1","9";"9","0";"0","A";"A","u";"u","s";"s","t";"t","r";"r","a";"a","l";"l","i";"i","a";"a","1";"1","4";"4","8";"8","4";"4",""}),{1;-1}),0)+1,LEN(A16))

…evaluates to this:

=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!} ),{1;-1}),0)+1,LEN(A16))


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!} ),{1;-1}),0)+1,LEN(A16))

…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},{1;-1}),0)+1,LEN(A16))


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},{1;-1}),0)+1,LEN(A16))

…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},{1;-1}),0)+1,LEN(A16))


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},{1;-1}),0)+1,LEN(A16))

…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},0)+1,LEN(A16))


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,LEN(A16))

…evaluates to this:

=MID(A16, 11,LEN(A16))
 
First solution posted:

http://chandoo.org/wp/2013/07/16/formula-challenge-001-1/
 
Hi, maybe it's my version of Excel (2010) but I get 0Australia1484 using the solution outlined.


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.
 
Hi John. Yes, there was a slight error in the download file at http://chandoo.org/wp/2013/07/16/formula-challenge-001-1/


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$2:INDEX(A:A,LEN(A1)+1)),1))>57),0),LEN(A1))
 
Over at the Chandoo.org writeup on this post, Elias came up with this:

=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 &57321^2 bit is pure genius. Basically it adds this to the end of the string:

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
 
Here's a shorter way to write a pandigital number than 57321^2

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!
 
PS: Oops, sorry Jeff. I came here for the first time via a link from Hui in the Excel Hero chatroom, and thought I had a clever formula to offer.


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.
 
Stephen, good stuff. Your formula points out where mine has a couple of unneeded additions.


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!
 
Next smallest approach (from Sajan) is written up here: http://chandoo.org/wp/2013/07/17/formula-challenge-001-return-everything-from-a-string-after-the-first-block-of-numbers-part-2/


(Don't worry Sajan...I cover your even shorter formula off in a future post)
 
Over at http://chandoo.org/wp/2013/07/17/formula-challenge-001-return-everything-from-a-string-after-the-first-block-of-numbers-part-2 David Hagar came up with this gem:

=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.
 
Hi Jeff,

Great, engaging writeup!


Very interesting technique by David Hager. I love the simplicity of that formula! Very elegant.

(The only limit I see with that approach is that it requires the character before the number to be an alphabet.)


Cheers,

Sajan.
 
3rd writeup posted over at http://chandoo.org/wp/2013/07/19/formula-challenge-001-return-everything-from-a-string-after-the-first-block-of-numbers-part-3/


Haseeb, you're now officially an Excel rock-star. Can I have your formula-graph? ;-)
 
Answers to this formula challenge continue to amaze me. Check out this submission from Elias over at chandoo.org/wp/2013/07/19/formula-challenge-001-return-everything-from-a-string-after-the-first-block-of-numbers-part-3


=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 numbers with this bit as well as offsets the numbers array by one all in one clever move:

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


...and then checks for text with this bit without offsetting the array:

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 the comma, these are equivalent:

=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.
 
Hi Jeff,

It turns out that my previous array formula could be optimized a bit:

=MID(A1,MATCH(1,MMULT(-ISERR(-MID(A1,ROW(A:A),{1,2})),{1;-1}),)+1,6^6)


Now at 70 characters... and I still got to keep my favorite MMULT function! :)


Cheers,

Sajan.
 
Sweet!

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

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.
 
That is freakin' awesome. Using it like this:

=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))
 
Latest earth-shattering revelations on this challenge now posted from the Earthquake Capital of the Southern Hemisphere at http://chandoo.org/wp/2013/07/22/formula-challenge-001-return-everything-from-a-string-after-the-first-block-of-numbers-part-4/
 
You must log in or register to reply here.
Back
Top