Find and extract string from a cell text

[eduzs]

I need a formula that finds and extract "month of 2020" in columns A.
Example:
Column A
1 Test
2 Hi
3 1
4 2
5 3
6 This , of course, is the month of december of 2020, ok.
7 Any text.
8 6
9 3
The formula should:
1) Find the cell that starts with "This, of course"; (the patern can appear anywhere in column A) = Row 6
2) Extract text from cell "A6" between the last "of" and the last ",", in this case = "december of 2020".
Is there a way to do that? Thanks.

 

[Fluff]

You should never use whole column references in a MATCH function.
That said there is nothing wrong with what you have posted.

 

[eduzs]

Is there a way to use MATCH if in the cell have more than 256 char? Like lookup only the first 20 characters of each cell.

 

[Fluff]

You could use

=MATCH("this",LEFT(A1:A2000,4),0)

 

[eduzs]

Thanks, works fine.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[eduzs]

You're welcome & thanks for the feedback.

Now I know the cell to extract the text (A50).
How can I extract "december of 2020" from cell A50?
Cell A50 text:
"... x of december of 2015, y...."
Text betweem first "x of " and first ", y" backwards.

 

[Fluff]

See post#7

 

[eduzs]

Thanks!

 

[Fluff]

My pleasure.