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!

 

[StephenCrump]

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

Not if there's more than one unique value.

This question was specifically for your XLOOKUP formula which gives an error for those instances when there are multiple identical matches ...

It won't error. It will find the first occurrence. If the other occurrences are identical, this will be the same value that UNIQUE/FILTER returns. XLOOKUP will only be a problem if you have more than one unique value to return.

I'd keep it simple and transparent, and avoid monster formulae. If your individual cell formulae in A:A might spill, create space to let them spill across the adjacent column(s). Then just point your GetDayNo formula to the individual cells. If you have 2, 3 ... + values for any one row, you'll need to decide what to do with them.

 

[Peter_SSs]

I either have one single match, or multiple matches that are identical

If that is the case, then you should be able to resolve the UNIQUE(FILTER.. issue by INDEXing the first value.

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

 

[StephenCrump]

If that is the case, then you should be able to resolve the UNIQUE(FILTER.. issue by INDEXing the first value.

I tested that earlier with a simpler FILTER construction.

INDEX( ... ,1,1) seems to do the trick.

 

[Rnkhch]

I see, thank you both, for the directions/formulas. I'll test and update.

Tomorrow is grand jury day , so I'll test on Thursday at work

 

[Rnkhch]

Success INDEX worked very well! Thanks

 

[Peter_SSs]

Cheers. Good news!