Excel – Convert Text Month to the Month Number

A trick to get a number from a name

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.

=MONTH(1&LEFT(A1,3))

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.

Month Number Formula

We could shorten this formula to

=MONTH(1&A1)

Because if you type 1September it also returns a date.

Note: in Australia we put the day before the month in our dates.

Please note: I reserve the right to delete comments that are offensive or off-topic.