Are you looking for a straightforward technique to transform month names into numerical representations within Excel?
If so, you’ve landed in the right place. Excel has options to format a cell as ‘Short Date’ and ‘Long Date’, but unfortunately lacks a direct selection specifically for months.
Therefore, working with ‘months’ in Excel requires the use of smart and effective techniques.
There are multiple ways to get this done, irrespective of your data size. In this article, you will learn three easy methods to convert month names into numbers in Excel.
The Top 3 Methods To Quickly Convert a Month Name to a Number
For the purpose of this article, we will use a specific dataset (shown below) to demonstrate the application of all the methods outlined for converting “month names” to “numbers”.
Method 1: Using MONTH Function
The MONTH function application is the easiest thing you will learn today. This function is ideal for data with the months represented as text. When the cells are not formatted as ‘date’, then the MONTH syntax needs to be partially tweaked to obtain perfect number representations.
The MONTH function Syntax:
MONTH (serial_number) Let’s check out the Step-by-Step Guide on how to use the MONTH function below:
Step 1– In place of “serial_number”, select the cell with the month name. For example, we will select A2, where January is present for our dataset.
Along with the cell number, we will add “&1”, and our formula will look like this:
=MONTH(A2&1)
We need to use this concatenation process in Excel to represent the normal text ‘January’ as a month.
Step 2– Press Enter, and the converted number will appear in cell B2 as ‘1’, representing the first month of a year.
Step 3– To apply the formula to all cells, drag the ‘+’ button in the B2 cell or copy (CTRL+C) and paste (CTRL+V) the formula to the remaining cells.
Note– For the “A2&1” section in the above MONTH formula used, you can select it and press the ‘F9’ key to check how this syntax region gets interpreted.
Also read: Get the Day Name from the Date in Excel
Method 2- Combining MONTH and DATEVALUE Function
Below is the dataset we will use to implement all the methods for “month name” to “number” conversion
Step 1– Input the formula
=MONTH(DATEVALUE(A2&1))
in cell B2. This action will give the month number for ‘January’ in cell B2 of our dataset.
Step 2– Press the Enter key to get the Month number for ‘January’ in cell B2 for our dataset.
Step 3– Using the ‘+’ button in the B2 cell corner, drag it down to obtain a similar result for all the cells or copy (CTRL+C) and paste (CTRL+V) the formula from B2 to the remaining cell
A serial number is given to the MONTH function by combining the DATEVALUE function. The DATEVALUE function converts a Date (present as a text) to a serial number. This serial number represents the specific date or month in Excel. Essentially, the DATEVALUE function facilitates the conversion of all month names to numbers.
“&1” has the same use case mentioned above in the “MONTH” formula. If you use only the DATEVALUE section of the below formula, a unique serial number will be generated
Method 3: Using TEXT Function
The syntax for the TEXT function will be:
=TEXT(value, format_text)
Here, “value” refers to the cell name you need to convert to the respective month number. In the “format_text” section, you need to specify the result format.
Thus, the formula for our dataset will be:
=TEXT((A2&1), “mm”)
OR
=TEXT((A2&1), “m”)
Here, “mm” represents the format of the month’s number representation; we will get the result as ‘1’ for January if we just use ‘m’. Or we can use “mm”, you can see the result displayed as “01”.
Implementation Steps:
Step 1– First, we need to format the B2 cell in a date and number format. Right-click and select ‘Format Cells’ (Screenshot #1). OR Press “Ctrl+1”, and a “Format cells” window will appear.
Step 2– Select the “custom” option. Then write “mmmm” within the “Type” section. Click on the “OK” button to save this change. This represents that the cell is being formatted in the month format.
Step 3– In cell B2, input the formula- =TEXT((A2&1),“mm”). Here “&1” has the same purpose as mentioned in the above methods.
Step 4– Press the Enter key for the final output of the converted number for “January” in cell B2.
Step 5– Using the ‘+’ button in the B2 cell corner, drag it down to obtain a similar result for all the cells OR copy (CTRL+C) and paste (CTRL+V) the formula from B2 to the remaining cells.
Note– In the formula used above, if you add “mmm” just instead of “mm”, you will have the shorter abbreviation of the month displayed in cell B2. For example, the Text formula with “mmm” used will output answers as ‘Jan’, ‘Feb’ and ‘Mar’.
Bonus Hack (Applicable in Short forms of Months)
You will sometimes come across months written with a period in the end. Example- “Jan.” when dealing with large chunks of month and date sheets in Excel,
Dealing with these monthly texts can be puzzling. Hence, here is an additional method to convert such Month names into numbers.
The aim is to make Excel process the columns without the period at the end. We have to use the LEFT and LEN functions together.
The formula to apply to such a dataset will be:
=MONTH(LEFT(A2,LEN(A2)-1)&1)
By using the “-1” at the end of LEN function we instruct to only take three characters and omit the last character while running the MONTH formula. Both LEN and LEFT function is used to return a number of characters in a given text string.
Learning this trick will help you manage complex situations with month-based datasets. You’ll notice that B2 displays the same ‘1’ as other methods:
When it comes to managing spreadsheets, there are many ways to convert month names into their corresponding numeric values.
Each formula has its own unique characteristics and mechanisms that make it suitable for different types of data manipulation and analysis tasks. Hence, carefully consider each formula outlined in this guide and apply them accordingly.
By practicing these formulas, you’ll learn a unique set of skills that can significantly improve your efficiency and accuracy when processing and analyzing data in Excel. With practice, you’ll be able to handle month-name-to-number conversion tasks with ease and convenience.
Other articles you may also like: