Excel: Extract Numbers from Date Strings

Question

I have several malformed date columns. I am trying to transform these columns into "month", "year", and "start_day" columns.

Here is an example sample:

January 13th, 2018
January 13th-14th 2018
January 5th-7th 2018
January 4th-8th 2018
December 9th-10th 2017
December 2nd-3rd 2017
December 2nd, 2017
December 2nd-3rd 2017
November 18th, 2017
November 18th-20th, 2017
November 17th-19th 2017
November 11th, 2017
November 11th-12th, 2017
November 11th-12th 2017

Note that sometimes there is a comma between the day abbreviation and the year, other times not. My desired output (for the day column) would be:

13
13
5
4
9
2
2
2
18
18
17
11
11
11

I am not concerned about the second date (to the right of the hyphen). I was able to get the month with LEFT(A1, 3) and the year with RIGHT(A1, 4). I cannot for the life of me figure out how to grab the first numerical value to the right of the month without resorting to regex. Any ideas?

Answer 1

=IF(ISNUMBER(1*MID(A1,FIND(" ",A1)+2,1)),MID(A1,FIND(" ",A1)+1,2),MID(A1,FIND(" ",A1)+1,1))*1