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

 

[Joe4]

Will the date always appear at the end of the input, in that particular format?
If not, please post examples of all the other differing formats that it could appear in.

 

[brncao]

Yes, it will be in that format.

 

[Joe4]

Here is one way. Let's say that you entry is in cell A1, and you want the two outputs in cells A2 and A3.
Enter this formula in cell A2:
=LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),400,100)),3)&"/"&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),400,100))
and then this formula in cell A3:
=A2&" "&RIGHT(A1,4)

 

[DanteAmor]

Input: "Payment received as of

Is it always that text and then the date or can there be different texts?

 

[Eric W]

Here's another approach:

Book1
	A	B	C
1	Payment received as of January 31, 2020	January	January 2020
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXT(AGGREGATE(14,6,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),18)+0,1),"mmmm")
C1	C1	=TEXT(AGGREGATE(14,6,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),18)+0,1),"mmmm yyyy")

But like Dante suggests, it might be susceptible to different text, especially if there's a large embedded number in the text, like an account number. Test it on a large sample of your data.

 

[brncao]

Wow I didn't think it would be that complex! I'll have a look at it tomorrow. The goal is to extract a date serial from that input. I can figure out the rest.

Just to give you guys better context, the user changes the report's as of date to whatever date it's reporting for. So you might have something like "Report as of mmmm d, yyyy" where the first couple characters to the left of the date never changes. Only "mmmm d, yyyy" changes.

Before anyone asks this question, "why not just create a separate cell with a date and concatenate that?" Unfortunately that would force the user to unhide and hide this row or column, which is not practical. I don't want anything hidden from the user especially if other cells are dependent on it.

When the user changes that input, everything else in the report will update. Whether it's "mmmm" or "mmm yyyy" or "mmmm yyyy" so long as I have the date serial. This avoids redundant entries where a user might forget to change "Dec 2019" to "Jan 2020" in other parts of the workbook.

Trying to extract the date serial from a sentence is where the challenge is. I hope that helps.

 

[Amit Tandon]

For cell A2 string, use below formula:

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

This will return "January 31, 2020". TEXT(ROW($1:$12)*29,"mmmm") - returns all the 12 months ie. {"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"}. The formula searches for occurrence of these in the cell A2 string and returns all characters after that.

 

[brncao]

Thank you all!

Amit's solution is flexible. However, Eric's solution is even more flexible and #Ref!-error-proof (in case a row got deleted, but Amit's solution can be rectified with INDIRECT function inside ROW). What I like about Eric's solution is that it accepts "mmm*" so it'll take "Sep", "Sept", and "September."

One more question. Is there a way to check if input is valid? "Septmber 30, 2020" returns 7/12/1905.

 

[Eric W]

That's why I warned against checking against a large sample of your data. There's really no way to handle every situation that might arise. The best I came up with (making an already awkward formula even more so) is:

Book4
	A	B	C
1	Payment received as of Septmber 30, 2020	No valid date found	No valid date found
2	Payment received as of September 30, 2020	September	September 2020
Sheet2

Cell Formulas
Range	Formula
B1:B2	B1	=IFERROR(TEXT(EXP(LN(AGGREGATE(14,6,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),18)+0,1)-"12/31/1999"))+"12/31/1999","mmmm"),"No valid date found")
C1:C2	C1	=IFERROR(TEXT(EXP(LN(AGGREGATE(14,6,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),18)+0,1)-"12/31/1999"))+"12/31/1999","mmmm yyyy"),"No valid date found")

In your example, the largest number the formula extracted was 2020, which translated to a date serial number of 7/12/1905. In this version of the formula, it will reject any number less than 12/31/1999 (36525).