Extract Word from a Sentence

[zhchow86]

Hi, I am new to Chandoo.org. I am facing a problem and trying to find a solution.

From this sentence: Draft Profitability Statement for July 2020, I want to extract only July. I used MID and FIND function but did not get any suitable results. Is there anyone who can give me a solution?

The formula I used is here:
+MID(A3, FIND(" ",A3)+1, FIND(" ",A3,FIND(" ",A3,FIND(" ",A3)+1))-1-FIND(" ",A3))

I think I am not understanding the formula properly. Again a solution is welcome. Thank you.

 

[vletm]

zhchow86
Would this work for You?
=TEXT(RIGHT(A3,LEN(A3)-FIND("for",A3)-3),"mmmm")

 

[zhchow86]

TEXT(RIGHT(A3,LEN(A3)-FIND("for",A3)-3),"mmmm")

Thanks for your reply and it works. Can you please explain your formula and why my formula is not working?

 

[vletm]

zhchow86
Yours: Your find always finds same " " position.
Mine: Solves from right that 'date'.
Shows with text 'date's month.

 

[zhchow86]

zhchow86
Yours: Your find always finds same " " position.
Mine: Solves from right that 'date'.
Shows with text 'date's month.

Many Thanks!

 

[Peter Bartholomew]

If you create a helper range or an array constant named 'months' containing a list of months
= LOOKUP(1000, SEARCH(months, String), months)

 

[shrivallabha]

Here's a variation based on @Peter Bartholomew's suggestion.
=LOOKUP(2^15,SEARCH(" "&TEXT(DATE(2020,1,1)+ROW($A$1:$A$12)*29,"mmmm")&" ",A2,1),TEXT(DATE(2020,1,1)+ROW($A$1:$A$12)*29,"mmmm"))

 

[navic]

If you want, try without helper column

Extract the last two words from the sentence, regardless of the length of the sentence.

=MID(A3,FIND(" ",A3,FIND("@",SUBSTITUTE(A3," ","@",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))-1),1))+1,256)

Extract Second Last word from a sentence

=TRIM(LEFT(RIGHT(SUBSTITUTE(A3," ",REPT(" ",300)),600),300))