Ever needed to convert the text Sep or September into a 9? Well Excel can convert text months into their respective numbers.
If you enter 1Sep in a cell Excel will convert that into the date 1/9/13 (Excel uses the current year). You can use that functionality to convert the text of a month into the month number.
Assume A1 contains the word September. The following formula will convert that into the number 9.
Using the & symbol joins the 1 to the first three characters of the cell or 1Sep. Excel recognises that as a date format and treats it like a date for the MONTH function to then extract the month number.
We could shorten this formula to
Because if you type 1September it also returns a date.
Note: in Australia we put the day before the month in our dates.