Need to convert month names to numbers in Excel? Recently, I compiled a list of dates from a friend’s website and faced this challenge. In this quick tutorial, I’ll guide you on how to change the text string to numerical data using Excel’s MONTH function. For instance, we’ll transform March into 3.
The Wrong Data Type
In this case, the post’s published date showed as “March, 2022”. And when I extracted the info, I pulled that text into a single field.
The problem was my friend couldn’t sort by the “Published date”. Since the data was text, the sort was alphabetical, starting with April. He wanted a separate cell for the month and year so he could do a time analysis.
Granted, this isn’t a complex scenario. I could have done a “search and replace” on the list of month names.
You can use the CELL function if you’re unsure of a cell’s format. If we were dealing with a date format, the result in cell D2 should’ve resulted with a “D” value and number for Date. As you can see below, I got a “G” for “general”.
Split the Published Date Column into 2 Columns
The first thing I needed to do was to split the Published Date column into Month and Year columns as requested. There are a number of ways to do this. If you’ve read my tutorial on how to split names in Excel, you know that the text to columns wizard is an easy solution.
I opted to split the Published date column into two columns by using both a comma and space for delimiters. And since I didn’t want a blank column with just the space, I ticked the consecutive delimiters box.
Using Excel MONTH Function
Now that I have isolated the month into a separate column, I can convert the month text value to its corresponding month number. The MONTH function, as defined by Microsoft states, “Returns the month, a number from 1 (January) to 12 (December).
I added an example spreadsheet you can use for practice called month-name-conversion.xlsx. You can find it at the bottom of the page.
- In Excel, click the cell where you want your results.
- From the Formulas tab, click Insert Function.
- On the Insert Function dialog, select the Date & Time Category from the drop-down list.
- In the Select a function list box, select MONTH.
- Click OK.
- In the Function Arguments dialog, enter your cell reference followed by &1 in the Serial_number textbox.
You’ll notice that Excel displays two values in the Function Arguments dialog box. The first is the Serial Number [B] which represents the number of days since 1/1/1900. Excel uses this number for various date calculations. Below that large number is a smaller value [C] which also shows in the Formula result field. That number should be between 1 and 12. It represents the month number.
- Click OK.
- Click the cell’s fill handle to copy your formula in cell D2 down the column.
.
Convert Month to Number Didn’t Work
One reason the Excel formula month name conversion may not work is Step 6 above. Sometimes people use a formula with just the cell reference for the Serial_number. This produces a #VALUE! error. If you look closely at the Function Arguments dialog, you won’t see any month number results.
The fix is to concatenate or append an integer to the month’s cell reference. In my case, I used “1”. You can wrap the number in quotes if you prefer. This string forces a date calculation using the current year.
=MONTH(B2&1)
Another reason this might not work is misspellings. If, for some reason, March was misspelled, you would also get a #VALUE! Error. The good news is this formula does work on abbreviated month names.