• 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

jeffreyweir

Active Member
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.
 

Shaun

Member
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
 

Sajan

Excel Ninja
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.
 

jeffreyweir

Active Member
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))
 

Hui

Excel Ninja
Staff member
First solution posted:

http://chandoo.org/wp/2013/07/16/formula-challenge-001-1/
 

John Broggio

New Member
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.
 

jeffreyweir

Active Member
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))
 

jeffreyweir

Active Member
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
 

jeffreyweir

Active Member
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!
 

Stephen Crump

New Member
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.
 

jeffreyweir

Active Member
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!
 

jeffreyweir

Active Member
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)
 

jeffreyweir

Active Member
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.
 

Sajan

Excel Ninja
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.
 

jeffreyweir

Active Member
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? ;-)
 

jeffreyweir

Active Member
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.
 

Sajan

Excel Ninja
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.
 

jeffreyweir

Active Member
Sweet!

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

Sajan

Excel Ninja
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.
 

jeffreyweir

Active Member
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))
 

jeffreyweir

Active Member
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/
 
Top