• 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

shrivallabha

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

Sajan

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

shrivallabha

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

Sajan

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

jeffreyweir

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

jeffreyweir

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

jeffreyweir

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

BruceW

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

sambu

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

yogi_raj_143

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

Dave2018

New Member
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)
 
Top