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]

With a string like this: j32gh23gewDay 1 an

Excel is treating 1*"1 a" as 1:00 am, i.e. a value of 1/24 = 0.041667.

Here's one way you could tweak your formula (only very lightly tested!):

=MAX(IFERROR(--MID(A1,MIN(IFERROR(FIND(SEQUENCE(10,,0),A1,SEARCH("Day",A1)),999)),SEQUENCE(LEN(A1))),0))

 

[Peter_SSs]

I'm assuming that the other results in post 1 are what you want?
I wasn't sure since the description below could be interpreted as wanting "123" from the first example, or "1" and "23" since all 3 digits occur after "Day"

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".

Another option might also be to employ a user-defined function like this.

Function FirstNums(sData As String, sLookfor As String) As Long
  With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Pattern = "(" & sLookfor & "\D*)(\d+)"
    FirstNums = .Execute(sData)(0).SubMatches(1)
  End With
End Function

Rnkhch.xlsm
	A	B	C
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		1
9	j32gh23gewDay 1an		1
10	dfeDay-1 gh23gew3		1
11	dfeDay-1 -gh23gew		1
12	dfeDay-(1) -gh23gew		1
13	abcDay - 123def		123
Sheet1

Cell Formulas
Range	Formula
C1:C13	C1	=FirstNums(A1,"Day")

 

[Rnkhch]

Thank you Stephen for solving the mystery. And it seems that your function is working very well in all the tests that I did

And thank you Peter for the fabulous function! (I only needed the numbers closer to "day" so your function does exactly what I needed.)

Now I'm thinking whose post should I mark as solution

 

[StephenCrump]

Now I'm thinking whose post should I mark as solution

We're both Australian, hence we worship the coin-flip. I'll have heads, thanks.

 

[Peter_SSs]

Now I'm thinking whose post should I mark as solution

It is not important which one you mark, but it would be good if you could mark one of them. You could take Stephen's suggested approach or perhaps mark the one that you ended up using in your project.

 

[Rnkhch]

I used Stephen's formula as I thought people at work may have an easier time understanding it rather than a VBA module. Also he's far behind you in solutions count lol

 

[bebo021999]

Alternative way for whom with Ex2016 or erlier (without SEQUENCE function)

=AGGREGATE(14,6,MID(MID(A1,SEARCH("Day",A1)+3,255),AGGREGATE(15,6,SEARCH({1,2,3,4,5,6,7,8,9},MID(A1,SEARCH("Day",A1)+3,255)),1),{1,2,3,4,5})+0,1)

 

[Peter_SSs]

I used Stephen's formula as I thought people at work may have an easier time understanding it

Fair enough & a good reason.

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.

Rnkhch.xlsm
	A	B	C	D	E	F	G
1		Post #:	3	1	2	8	9
2	abcday1e2f3g		1	100	100	100	1
3	abcday12dec24gh		12	45638	45638	44907	12
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=FirstNums(A2,"Day")
D2:D3	D2	=LOOKUP(10^6,1*MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",SEARCH("Day",""&A2&""))),{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}))
E2:E3	E2	=MAX(IFERROR(--MID(A2,MIN(IFERROR(FIND(SEQUENCE(10,,0),A2,SEARCH("Day",A2)),999)),SEQUENCE(LEN(A2))),0))
F2:F3	F2	=AGGREGATE(14,6,MID(MID(A2,SEARCH("Day",A2)+3,255),AGGREGATE(15,6,SEARCH({1,2,3,4,5,6,7,8,9},MID(A2,SEARCH("Day",A2)+3,255)),1),{1,2,3,4,5})+0,1)
G2:G3	G2	=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)

 

[Rnkhch]

This is an incredible analysis and very informative and educational; thank you! Both your functions are rock solid. You are an absolute genius

I'll mark it as solution