Extract Month Name from Cell

[brncao]

Hey guys. I was wondering if there's a way to extract the name of the month from a cell. I'd like to figure out the two formulas, one with just the month name, and the other with the month and year.

For example,
Input: "Payment received as of January 31, 2020"
Output: Jan/January
Output: Jan/January 2020

Thanks

 

[brncao]

Thank you!

 

[Amit Tandon]

To return the Month name:

=IFERROR(TEXT(--MID(A2,AGGREGATE(14,6,SEARCH(TEXT(ROW($1:$12)*29,"mmmm"),A2),1),255),"mmmm"),"Invalid Date")

This will return "January"


To return the Month Name and Year:

=IFERROR(TEXT(--MID(A2,AGGREGATE(14,6,SEARCH(TEXT(ROW($1:$12)*29,"mmmm"),A2),1),255),"mmmm yyyy"),"Invalid Date")

This will return "January 2020"


And if there is no valid date then the formula will return ""Invalid Date".

To include search for abbreviated names of "Jan", "Feb" etc. you may use "mmm" instead of "mmmm" within the SEARCH function.