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.  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

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