Sometimes you may need to convert 3 letter month to a number in Excel. Furthermore, for any numerical calculation, you want to use January in your formula but January is an arbitrary name so you have to convert it into a number. Hence, I will explain how to convert 3 letter Month to number in Excel in this article.
How to Convert 3 Letter Month to Number in Excel: 8 Methods
Here, I will describe 8 methods to convert 3 letter month to a number in Excel. In addition, for your better understanding, I’m going to use the following dataset. Which contains 2 columns. They are Income and Month.
1. Using MONTH Function to Convert 3 Letter Month to Number in Excel
You can use the MONTH function to convert 3 letter month to a Number in Excel. MONTH function is a built-in function in Excel. With the help of this MONTH function, you can easily convert a month to a number.
Steps:
- Firstly, you have to select a cell, where you want to keep the result. I have selected the D5 cell.
- Secondly, you need to use the corresponding formula in the D5 cell.
=MONTH(C5&1)
Formula Breakdown
- Here, the MONTH function will return a month as a number of 1 (January) to 12 (December).
- C5&1: Here, Ampersand(&) combines Month nad value which denotes a date.
- MONTH(C5&1): will return the month value of February as 2.
- Now, press ENTER to get the month in number format.
- Now drag the Fill Handle icon to paste the used formula to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.
Finally, you will get all the converted Month values.
Read More: How to Convert Month to Number in Excel
2. Applying MONTH and DATEVALUE Functions
You can apply a combination of two functions, the MONTH function, and the DATEVALUE function to convert the 3 letter Month into a number in Excel. The steps are given below.
Steps:
- Firstly, you have to select a cell, where you want to keep the result. I have selected the D5 cell.
- Secondly, you need to use the corresponding formula in the D5 cell.
=MONTH(DATEVALUE(C5&1))
Formula Breakdown
- C5&1 = Feb1: Here, Ampersand(&) combines the value of C5 and 1.
- DATEVALUE(C5&1)=44592: The DATEVALUE function converts the text to a numerical value which represents a date
- MONTH(DATEVALUE(C5&1)) = 2: Finally, the MONTH function give the month value of the date.
- In the end, you must press ENTER to get the result.
- Now, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
Finally, you will see all the converted months in number format.
3. Employing MONTH and LEFT Functions
Again, you can employ combined functions to convert the 3 letter month to number in Excel. Here, you can combine the LEFT function with the MONTH function.
Steps:
- Firstly, you have to select a cell, where you want to keep the result. I have selected the D5 cell.
- Secondly, you need to use the corresponding formula in the D5 cell.
=MONTH(1&LEFT(C5,3))
Formula Breakdown
- LEFT(C5,3)= Feb: The LEFT function will extract the leftmost 3 characters of the value of the C5 cell.
- 1&LEFT(C5,3) = 1Feb: Now, Ampersand(&) adds 1 in front with the previous output to make a date.
- MONTH(1&LEFT(C5,3)) = 2: Finally, the MONTH function will extract the month value from the date.
- Now, you must press ENTER to get the result.
- Now, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
At this time, you will see the following result. Which are the converted Months.
4. Using MONTH Function with Symbol
You can not only use the functions but also use the Double Hyphen (–) symbol to convert the 3 letter Month to a number in Excel. In addition, you can combine the MONTH functions with the Double Hyphen (–) symbol. The steps are given below.
Steps:
- Firstly, you have to select a cell, where you want to keep the result. I have selected the D5 cell.
- Secondly, you need to use the corresponding formula in the D5 cell.
=MONTH(--(C5&1))
Formula Breakdown
- C5&1 = Feb1: Ampersand(&) combines the value of C5 and 1.
- –(C5&1) = 44593: The double dash/ hyphen works like the DATEVALUE function converts the text to a numerical value that represents a date.
- MONTH(–(C5&1)) = 2: Finally, the MONTH function gives the month value of the date.
- At this time, you must press ENTER to get the result.
- Now drag the Fill Handle icon to paste the used formula to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.
Lastly, you will get all the converted months.
Read More: Excel Formula to Find Date or Days for Next Month
5. Employing MATCH Function to Convert 3 Letter Month to Number
You can use the MATCH Function to convert 3 letter month to a number in Excel. Follow the steps below-
Steps:
- Firstly, you have to select a cell, where you want to keep the result. I have selected the D5 cell.
- Secondly, you need to use the corresponding formula in the D5 cell.
=MATCH(C5,{"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},0)- Subsequently, you need to press ENTER the get the result.
Formula Breakdown
MATCH(lookup_value,lookup_array,[match_type])
- lookup_value = C5: This is the look-up value for which the Match function will search in the array.
- lookup_array = {“jan”,”feb”,”mar”,”apr”,”may”,”jun”,”jul”,”aug”,”sep”,”oct”,”nov”,”dec”} : This is the look-up array where the MATCH
- [match_type] = 0 : The function will search for the exact match.
- Now drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcutsCtrl+C and Ctrl+V to copy and paste.
At last, you will get the converted months in number format.
6. Applying TEXT Function to Convert 3 Letter Month to Number in Excel
You can apply the TEXT function to convert the 3 letter Month to number in Excel. The steps are given below.
Steps:
- Firstly, you have to select a cell, where you want to keep the result. I have selected D5 cell.
- Secondly, you need to use the corresponding formula in the D5 cell.
=TEXT((C5&1),"m")
Formula Breakdown
- C5&1 = Feb1 : Ampersand(&) combines the value of C5 and 1.
- TEXT((C5&1),”m”) = 2: Now, the TEXT function will extract the month value from “Feb1”. Here “m” denotes the month value.
- At this time, you must press ENTER to get the result.
- Now, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
Lastly, you will see the converted Months in number format.
7. Using User Defined Function to Convert 3 Letter Month to Number
The most interesting part is that you can build your own function to convert the 3 letter Month to number in Excel. Moreover, you can employ the VBA code to develop a defined function. The steps are given below.
Steps:
- Firstly, you have to choose the Developer tab >> then select Visual Basic.
- Now, from the Insert tab >> you have to select Module.
- At this time, you need to write down the following Code in the Module.
Function ConvertMonth(givenMonth As String)
ConvertMonth = Month(DateValue("1 " & givenMonth & " 2022"))
ConvertMonth = Format(ConvertMonth, "0")
End Function
Code Breakdown
- Here, I have created a Function named ConvertMonth.
- Next, I have declared a variable givenMonth as a String to call the Months.
- Now, using the Month and DateValue functions, I have developed the function named ConvertMonth. Also, I have used the Format function to format the result.
- Now, you have to save the code.
- Then, you need to go to the Excel worksheet.
At this time, you can use your defined function. For this, you should follow the given steps.
Steps:
- Now, you have to select a cell, where you want to keep the result. I have selected the D5 cell.
- Then, you need to use the corresponding formula in the D5 cell.
=ConvertMonth(C5)
Here, ConverMonth will convert the arbitrary Month name into a number format.
- Now, you must press ENTER to get the result.
- Now, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
Finally, you can see the converted Months in number format.
8. Use of VLOOKUP Function to Convert 3 Letter Month to Number in Excel
You can use the VLOOKUP function to convert a 3 letter month to a number in Excel. Follow the steps below-
Steps:
- Firstly, you have to select a cell, where you want to keep the result. I have selected the D5 cell.
- Secondly, you need to use the corresponding formula in the D5 cell.
=VLOOKUP(C5,$F$5:$G$16,2,FALSE)
Formula Breakdown
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
- lookup_value = C5: The value that it looks for in the leftmost column of the given table.
- table_array = $F$5:$G$16: The table in which it looks for the lookup_value in the leftmost column.
- col_index_num = 2: The column number in the table from which a value is to be returned.
- [range_lookup] = FALSE: 0 or False for an exact match, 1 or True for a partial match. So, it denotes the exact match for the lookup_value
- Subsequently, you need to press ENTER to get the value.
- Now, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.
Lastly, you will get the converted Months in number format.
💬 Things to Remember
- For all those methods, there is no restriction to capitalize the letters.
- In the case of using the VLOOKUP function, you need to make your own array table.
- Furthermore, in the case of using the MATCH function, you need to mention all the 3 letter Months in the formula.
Practice Section
Now, you can practice the explained method by yourself.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 8 methods of converting 3 Letter month to number in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Get First Day of Month from Month Name in Excel
- How to Calculate First Day of Previous Month in Excel
- Get the First Day of the Current Month in Excel
- Excel Formula for Current Month and Year
- How to Get Last Day of Previous Month in Excel
- Excel VBA: First Day of Month
- How to Get the Last Day of Month Using VBA in Excel
<< Go Back to Excel MONTH Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
