Trying to optimize this function to find numbers after a specific text

[Rnkhch]

Hello,

I found this formula online and adapted it to some extent, and I'm trying to figure out how it works, but also why I get some strange results.

My goal is to find and extract only the numbers that occur after a specific text in a string, in this case the word "day". So in this example, I need the output to be "1". I don't want the numbers that are further to the right of the numbers that are closest to "day". Here in the case of A8, I get a strange output, and wonder why.

Of course, if there are other better formulas, please let me know as well.

Book1
	A	B	C	D
1	dfeDay1 gh23gew		1
2	Day 1 gh23gew45		1
3	gejxqwqDay 1gh23gew		1
4	dfegejday 1 )gh23gew		1
5	dfegej32day 1 )gh23gew		1
6	dfegej32gh23gewDay 1		1
7	dfegej32gh23gewDay 1a		1
8	j32gh23gewDay 1 an		0.041667
9	j32gh23gewDay 1an		1
10	dfeDay-1 gh23gew3		1
11	dfeDay-1 -gh23gew		1
12	dfeDay-(1) -gh23gew		1
13
Sheet1

Cell Formulas
Range	Formula
C1:C12	C1	=LOOKUP(10^6,1*MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",SEARCH("Day",""&A1&""))),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25}))

Thanks for any input!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[StephenCrump]

Just been thinking about it a bit more though, and not knowing anything about your real data, all the formula methods (so far at least) could give you some unexpected results I think - see below.
I have suggested another formula solution in column G that should eliminate such a possibility.

Nice one! Thanks Peter.

It's reminiscent of the problem geneticists have with Excel being "helpful":

Gene name errors are widespread in the scientific literature - Genome Biology

 

[Peter_SSs]

Cheers Stephen.
Hope things are all good down your way!

That article looked vaguely familiar so I was wondering if you may have pointed me to it previously. But no, I think that it must have been this post.

 

[StephenCrump]

Cheers Stephen.
Hope things are all good down your way!

You too, thanks Peter. All good down here.

Sulking just a little to have a solution stripped from me ...

I used Stephen's formula ... Also he's far behind you in solutions count lol

... but I will happily concede this one. Nice formula!

 

[Rnkhch]

It's interesting you brought this up. I'm a scientist, and I've definitely seen this happening.

But here's a another mind-boggling issue that I'm encountering with your newest formula in G2:

So I have this function that evaluates to "Day 5" as a single cell, not a spill:

A2=UNIQUE(FILTER(FMBMcReqDay,(FMBMcGeqSID=B35)*(FMBMcIeqReq=D35)*(ISNUMBER(SEARCH("sample",FMBMcL)))*(ISNUMBER(SEARCH("sputum",FMBMcPeqSpec)))*(ISNUMBER(SEARCH("Day",FMBMcReqDay)))))

Now, when I use your formula such that it refers to cell A2, it works perfectly fine, and I get "5":
=LET(r,REPLACE(A2,1,SEARCH("day",A2),""),ch,MID(r,SEQUENCE(LEN(r)),1),LEFT(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(ch+0),ch," ")))," ",REPT(" ",20)),20)+0)

However, if I substitute the two occurrences of "A2" within the formula with the actual long formula of mine above, as shown below, then I get #VALUE! error (A value used in the formula is of the wrong data type):

=LET(r,REPLACE((UNIQUE(FILTER(FMBMcReqDay,(FMBMcGeqSID=B35)*(FMBMcIeqReq=D35)*(ISNUMBER(SEARCH("sample",FMBMcL)))*(ISNUMBER(SEARCH("sputum",FMBMcPeqSpec)))*(ISNUMBER(SEARCH("Day",FMBMcReqDay)))))),1,SEARCH("day",(UNIQUE(FILTER(FMBMcReqDay,(FMBMcGeqSID=B35)*(FMBMcIeqReq=D35)*(ISNUMBER(SEARCH("sample",FMBMcL)))*(ISNUMBER(SEARCH("sputum",FMBMcPeqSpec)))*(ISNUMBER(SEARCH("Day",FMBMcReqDay))))))),""),ch,MID(r,SEQUENCE(LEN(r)),1),LEFT(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(ch+0),ch," ")))," ",REPT(" ",20)),20)+0)

Any idea why this could be happening?

 

[StephenCrump]

Any idea why this could be happening?

In a word, it's FILTER() that's causing the problem.

I haven't looked at modifying Peter's formula, but if your UNIQUE(FILTER(...)) will always return a single value, you could use XLOOKUP or INDEX/MATCH instead:

=XLOOKUP(1,(FMBMcGeqSID=B35)*(FMBMcIeqReq=D35)*ISNUMBER(SEARCH("sample",FMBMcL))*ISNUMBER(SEARCH("sputum",FMBMcPeqSpec))*ISNUMBER(SEARCH("Day",FMBMcReqDay)),FMBMcReqDay)

However, if I substitute the two occurrences of "A2" within the formula with the actual long formula of mine above ....

You're backsliding! I've seen you on another thread happily using LET() to eliminate all double occurrences.

Rather than building monster formulae, I suggest you try going the other way - build Peter's formula into a LAMBDA for easy repeat use.

 

[Rnkhch]

Thanks! Yeah, I haven't forgotten LET lol I was first trying to see what was causing the error. But I will totally do the LET and LAMBDA once I figure everything out.

So I checked your XLOOKPUP formula, and it works fine when there is only one matching cell. What should I do if there are more than one identical matches?

That's the situation I'm at with my big data. I either have one single match, or multiple matches that are identical (but so far I haven't seen multiple different matches).

 

[StephenCrump]

What should I do if there are more than one identical matches?

You tell us!

So far, we've assumed your "Day" cells are single values.

If A1 is a spill formula, you could do:

=BYROW(A1#,LAMBDA(r,GetDayNo(r)))

after you've created your GetDayNo LAMBDA.

 

[Rnkhch]

So far, we've assumed your "Day" cells are single values.

Yes, they have been single because I've been using UNIQUE which causes the FILTER to not spill.

What should I do if there are more than one identical matches?

This question was specifically for your XLOOKPUP formula which gives an error for those instances when there are multiple identical matches, so maybe I should have asked, If I don't use FILTER and instead use XLOOPKUP, how can I combine UNIQUE with XLOOKUP

If A1 is a spill formula, you could do

I hope to keep everything in A as non-spill single cells, but I am playing with your BYROW suggestion for my own learning


But to recap things, I'm at the stage of posts #15 and #16 where FILTER is giving the #VALUE! error and XLOOKUP only partially works (i.e. for single matches) and gives #N/A! error (A value is not available for the formula or function) for when there are multiple identical matches.

So I need a function to put in place of the two occurrences of A2 in Peter's formula such that the whole formula doesn't lead to any error.

Perhaps a conditional statement like this to go in place of the two A2 occurrences through a LET (where I've highlighted Peter's formula):

D2=LET(y,LET(x,XLOOKUP(1,(FMBMcGeqSID=B35)*(FMBMcIeqReq=D35)*ISNUMBER(SEARCH("sample",FMBMcL))*ISNUMBER(SEARCH("sputum",FMBMcPeqSpec))*ISNUMBER(SEARCH("Day",FMBMcReqDay)),FMBMcReqDay),IF(ISERROR(x),(use another function),x)),LET(r,REPLACE(y,1,SEARCH("day",y),""),ch,MID(r,SEQUENCE(LEN(r)),1),LEFT(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(ch+0),ch," ")))," ",REPT(" ",20)),20)+0))

And I need that "another" function lol

And of course I would eventually put the whole thing in a LAMBDA.

Does my reasoning sound good?

 

[Rnkhch]

P.S.

Of course, if there could be a single function to be "y", then so much the better as there would be no need for that conditional statement in my example above