• 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

JW,


Superb write up. Now if we could have analyse those formulas for their speeds as well then we may find out fastest sword wielding Samurai ;)


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

is shorter but costly formula [from calculation point of view) compared to:

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


Just to throw in a perspective :D
 
Hi Shrivallabha,

In real life, I would never endorse an all-column calculation (except when the function used is smart enought to detect the used range). When I created the formula, I used ROW(A1:A30) since I knew that my strings were not larger than that! (For that matter, I would never use omitted parameters either since that makes the formulas difficult to understand and debug!)


To me, these challenges are more about learning about yet another way to do something, instead of looking for the shortest formulas.


-Sajan.
 
Hi Sajan,


Your point is taken, Sir. In fact, I have doffed my hat so much that it is worn out and has become utterly useless towards its primary function of covering my head.


9 out of 10 times, I trip over your formulas.


In above case, I just wanted to throw in efficiency perspective since otherwise it was sliding towards reducing the length. This I have seen in the past makes people think harder for every argument they write in the formula.


In the past, I never cared for formula efficiency as long as they worked until one day a simple VLOOKUP on large data took ages to calculate and discovered this site in particular.

http://www.decisionmodels.com/calcsecrets.htm
 
Hi Shrivallabha,

You are too kind! But there is no need for any "hat doffing"... we are all in this to learn something new... I have certainly learned a ton going through these challenges.


(I don't typically care too much about formula efficiency either, probably because of the small data sets I typically work with. However, it is good to learn about them. Thanks for sharing the link to the decisionmodels site.)


In some respects, I am not sure if the drive to shorter formulas is a good thing from a learning perspective since it probably discourages others from submitting new / other ideas. In case anyone is holding back on ideas, here is my request to readers to submit ideas for solving the challenges, regardless of the length of such solutions.


Hi Jeff,

By the way, one thing that should probably get included in the writeups is a limitation of a given approach. For example, the following formula would not return the desired results on a string such as "First12345Second6789Third4567". While this was not explicitly called out as a requirement in the challenge description, it is good to understand the limits of a given approach.


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


to get the desired results, one will need to tweak it as follows:

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


(Of course, this is because of the way MATCH works when the last parameter is omitted.)


Perhaps you can devote a whole article to describe the limits of the various formulas! Just ensuring you have a steady stream of topics to write about! :)


Regards,

Sajan.
 
Thanks, Haseeb. It's amazing how time consuming this is. But it's worth it, because it forces you to be really clear about how an approach works. And thank you for responding to these challenges...without the hive, there is no hive mind.
 
Stef@n has posted a very clever approach 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/#comment-441204


He splits the string into progressively longer bits from the right hand side so he can find the start of that last batch of numbers, and then truncates the string from the left so that those numbers are excluded. He then searches for the position of the last number. Ingenious.


I’ve changed it so it doesn’t use whole column references, in case anyone wants to evaluate it bit by bit:

=MID(A1,LOOKUP(,-MID(9&LEFT(A1,LEN(A1)-(LEN(LOOKUP(,-RIGHT(A1&9,COLUMN(OFFSET(A1,,,,LEN(A1))))))-2)),COLUMN(OFFSET(A1,,,,LEN(A1))),1),COLUMN(OFFSET(A1,,,,LEN(A1)))),9^9)


He posted a shorter formula, but it doesn't work...I presume the wordpress parser ate some of it.
 
And here's another great approach from Stef@n:

Code: 
=MID(A1,MATCH(2,1/(CODE(MID(9&A1,COLUMN(A1:Z1),1))<58)/(CODE(MID(A1,COLUMN(A1:Z1),1))>64),1),99)

Notice the 9 in 9&A1 above. This extra character creates an array that is one element out of alignment with the original array, so that when he converts the two arrays to true/false and divides one by the other, he can find the position where text ends and numbers starts.

Edit:

This can be simplified further to:

Code: 
=MID(A1,1+MATCH(0,MID(A1,ROW(OFFSET(A1,,,LEN(A1),)),1)-MID(" "&A1,ROW(OFFSET(A1,,,LEN(A1),)),1),),9^9)

Will definitely include this in my next article in this series.
 
HI Jeff

Heres my attempt - without looking at the above

=MID(A3,MATCH(1,((CODE(MID(A3,ROW(INDIRECT("2:"&LEN(A3))),1)))&#62;64)*(CODE(MID(A3,ROW(INDIRECT("1:"&LEN(A3)-1)),1))&#60;58),0)+1,LEN(A3))

131 char


I see it has already been dealt with but stoked I came anywhere close to your original attempt
 
=MID(A1,SMALL(IF(IFERROR(MID(A1,ROW($1:$250),1)*1,"No")="No",ROW($1:$250),9^9),MATCH(1,IF(MID(A1,ROW($1:$250),1)*1>=0,1,0),0)),256)

Try this array formula
 
=RIGHT(A1,LEN(A1)-MIN(IFERROR(FIND(CHAR(64+ROW($1:$58)),A1,MIN(IFERROR(FIND(ROW($1:$10)-1,A1,1),""))),""))+1)


May not be optimal still....
 
this is an old post, but was interested...

here is my 59 character formula:

=MID(A1,AGGREGATE(15,6,FIND(CHAR(ROW($65:$90)),A1,2),1),99)
 
Tried a 365 formula on this. I knew I was going in the wrong direction when I hit 522 characters and rising. I got the idea that I hadn't entered into the spirit of the challenge when I saw that 160 of those were space characters! The final worksheet formula looked OK but then, there were the naughty illegal Named Lambda functions to account for!
Code: 
= MAP(list, Block2onwardλ)
The only saving grace was that the formula returned results for the entire list.

Interestingly, I note that in the final entry Jeff had predicted the separation of the UK from the EU, 3 years before the referendum!
 
You must log in or register to reply here.
Back
Top