Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 3.)

Welcome back to yet another gripping episode of “When good formulas go GREAT”. We’re working up the singles charts to the number one hit “Formula Challenge 001 – Return everything in string after first block of numbers” By way of a quick refresher, this formula challenge calls for a formula to return a substring from a longer string. Those strings look something like this: String: Required Substring: Monaco7190Australia1484 Australia1484 Liechtenstein42Austria128590 Austria128590 Malta6Belarus78 Belarus78 So far we’ve heard from these crooners: Jeff: Formula Challenge 001 – Part 1 Sajan: Formula Challenge 001 – Part 2 You can download the challenge and full dataset here: Formula Challenge 1.3 Specifically, we need a formula to return only the second country and associated number …in the case of Monaco7190Australia1484 it’s that bit in bold i.e. Australia1484 Part 3 Yesterday, Sajan and his band performed this hot Latin-infused number: =MID(A1,MODE(MMULT((N(ISNUMBER(-MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))={1,0})*(ROW(INDIRECT(“1:”&LEN(A1)))-{0,1}),{1;1}))+1,LEN(A1)) Today, Haseeb shakes his groove thing in return, to the tune of this hard-rock anthem: =MID(A1,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1,MIN(IFERROR(FIND(ROW($1:$10)-1,A1),”"))+1),”")),LEN(A1)) Rock and roll, baby! Haseeb’s orchestration is really clever: 1. Find the position where every number in the string occurs: Here’s how he does that: =FIND(ROW($1:$10)-1,A1) =FIND({1;2;3;4;5;6;7;8;9;10} -1,A1) =FIND({0;1;2;3;4;5;6;7;8;9} , A1) ={10;8;#VALUE!;#VALUE!;21;#VALUE!;#VALUE!;7;22;9} 2. Remove the errors… =IFERROR({10;8;#VALUE!;#VALUE!;21;#VALUE!;#VALUE!;7;22;9},”") ={10;8;”";”";21;”";”";7;22;9} 3. …so that we can work out which of these numbers occurs first in the string: He does this by taking the MIN of them. Just like the judges on X-Factor, MIN doesn’t handle errors. Which is why he removed them in the last verse. =MIN({10;8;”";”";21;”";”";7;22;9}) =8 So he’s already worked out where that first block of numbers begins – position 8 – meaning he can completely ignore any text that occurs before this position. 4. Get the start of the second block: Now he can search from position 8 for the very next letter. That letter will be the start of the text block we’re after. Ingenious. =SEARCH(CHAR(ROW($65:$90)),A1,8) =SEARCH({“A”;”B”;”C”;”D”;”E”;”F”;”G”;”H”;”I”;”J”;”K”;”L”;”M”;”N”;”O”;”P”;”Q”;”R”;”S”;”T”;”U”;”V”;”W”;”X”;”Y”;”Z”},A1,8) ={11; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 18; #VALUE!; #VALUE!; 17; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 15; 13; 14; 12; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}  5. Get rid of those errors with IFERROR: =IFERROR({11; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 18; #VALUE!; #VALUE!; 17; #VALUE!; #VALUE!;#VALUE!; #VALUE!; #VALUE!; 15; 13; 14; 12; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!},”") ={11;”";”";”";”";”";”";”";18;”";”";17;”";”";”";”";”";15;13;14;12;”";”";”";”";”"} 6. Work out which of these letters occurs first in the string: Again, he does this by taking the MIN of them: =MIN({11;”";”";”";”";”";”";”";18;”";”";17;”";”";”";”";”";15;13;14;12;C”;”";”";”";”"}) =11 7. Split the string from that point forward: =MID(A1,11,LEN(A1)) =Australia1484 Ahh, Australia again. ACDC, anyone? TNT, He’s dynamite TNT and his formulas are tight TNT, He’s an Excel nerd TNT, A shorter formula would be absurd! …or would it ? Tune in same time on Monday and find out. And bring your air guitar this time. About the Author. Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this: =NOT(EVEN(PROPER(OR(RIGHT(TODAY()))))) That’s right, pure #VALUE!