Find line in cell which contains multiple lines

[Mongy]

Have a leave report which is auto produced from a payroll system which would look something like this, let's say in A2.
Looking to see if there is a formula which can do this, if not VBA.

From Joe Bloggs 06-JUL-2022

Unimportant text
More Unimportant text
From Wednesday, 6 July 2022 to Friday, 8 July 2022
Yet more unimportant text

The number of lines in the cell may vary, there may be multiple lines which commence with the word FROM, and the line number in the cell may vary (eg won't always be the 5th line).
Need to extract the line where it may commence with "From Monday" (or any other day of the week), or may commence with "For one day on Tuesday" (or any other day of the week).
The text searching for in the line above should be unique in all the lines, except possibly line one, which could be a response From Wednesday Addams, so the text we're trying to find will not be in line one.

So would end up with a return of From Wednesday, 6 July 2022 to Friday, 8 July 2022.
From here, I would just add a macro to remove From, the days of the week, replace " to " with character and then text to columns, and will end up with two columns with 6 July 2022 and 8 July 2022.

Any help/pointers/suggestions would be appreciated
Cheers

 

[BSALV]

FromTo.xlsx
	A	B	C	D	E	F	G	H	I	J
1	From Joe Bloggs 06-JUL-2022						From Wednesday, 6 July 2022 to Friday, 8 July 2022	6 July 2022	8 July 2022
2
3	Unimportant text
4	More Unimportant text
5	From Wednesday, 6 July 2022 to Friday, 8 July 2022
6	Yet more unimportant text
7
8
Blad1

Cell Formulas
Range	Formula
G1	G1	=FILTER(A1:A100,ISNUMBER(SEARCH("from",A1:A100))*ISNUMBER(SEARCH("to",A1:A100))*ISNUMBER(SEARCH(",",A1:A100)))
H1	H1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($G1," to ",","),",",REPT(" ",100)),100,100))
I1	I1	=TRIM(RIGHT(SUBSTITUTE($G1,",",REPT(" ",100)),100))

 

[Mongy]

Sorry, I meant all of that text was in the one cell A2. I just want to extract the “From Wednesday” (any day) etc line out.

 

[BSALV]

i can give you a VBA-solution, but I'll wait, perhaps somebody else with better 365-formula-skills ?

 

[BSALV]

is there a linefeed between each line in A2 ?

 

[Mongy]

is there a linefeed between each line in A2 ?

Yes, there is a linefeed for each line.

 

[Peter_SSs]

Looking to see if there is a formula which can do this, if not VBA

From here, I would just add a macro to remove From, the days of the week, replace " to " with character and then text to columns

If you are going to finish it off with a macro, why not do it all with a macro?

 

[Peter_SSs]

You could see if this formula works for you (One example is not much to test with )

Mongy.xlsm
	A	B	C
1
2	From Joe Bloggs 06-JUL-2022 Unimportant text More Unimportant text From Wednesday, 6 July 2022 to Friday, 8 July 2022 Yet more unimportant text	6 July 2022	8 July 2022
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LET(p,AGGREGATE(15,6,SEARCH({"January","February","March","April","May","June","July","August","September","October","November","December"},A2,FIND(CHAR(10),A2)),1),DATEVALUE(MID(A2,p-3,FIND(" ",A2,p)-p+8)))
C2	C2	=LET(p,AGGREGATE(15,6,SEARCH({"January","February","March","April","May","June","July","August","September","October","November","December"},A2,SEARCH(TEXT(B2,"d mmmm yyyy"),A2)+3),1),DATEVALUE(MID(A2,p-3,FIND(" ",A2,p)-p+8)))

 

[Mongy]

If you are going to finish it off with a macro, why not do it all with a macro?

I'm normally better with excel formulas more than I am with VBA.
Thank you, try it out

 

[Peter_SSs]

@Mongy
I was just notified that you had marked my solution in this thread & it made me look at the problem again. I noticed that we can shorten those formulas quite a bit by not having to spell out all those month names in full if you are interested.

Mongy.xlsm
	A	B	C
1
2	From Joe Bloggs 06-JUL-2022 Unimportant text More Unimportant text From Wednesday, 6 July 2022 to Friday, 8 July 2022 Yet more unimportant text	6 July 2022	8 July 2022
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LET(p,AGGREGATE(15,6,SEARCH(TEXT("1/"&SEQUENCE(12),"mmmm"),A2,FIND(CHAR(10),A2)),1),DATEVALUE(MID(A2,p-3,FIND(" ",A2,p)-p+8)))
C2	C2	=LET(p,AGGREGATE(15,6,SEARCH(TEXT("1/"&SEQUENCE(12),"mmmm"),A2,SEARCH(TEXT(G2,"d mmmm yyyy"),A2)+3),1),DATEVALUE(MID(A2,p-3,FIND(" ",A2,p)-p+8)))