Convert 3 Letter Month to Number in Excel (8 Suitable Methods)

Sometimes you may need to convert 3 letter Month to a Number in Excel. Furthermore, for any numerical calculation like 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.


Download Practice Workbook

You can download the practice workbook from here:


8 Methods to Convert 3 Letter Month to Number in Excel

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.

Convert 3 Letter Month to Number Excel


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

Convert 3 Letter Month to Number Excel

Finally, you will get all the converted Month values.

Convert 3 Letter Month to Number Excel

Read More: How to Convert Month to Number in Excel (3 Easy Methods)


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

Convert 3 Letter Month to Number Excel

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

Convert 3 Letter Month to Number Excel


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.

Convert 3 Letter Month to Number Excel

  • 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))

Convert 3 Letter Month to Number Excel

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 give 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 respectively to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.

Convert 3 Letter Month to Number Excel

Lastly, you will get all the converted Months.


Similar Readings


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.

Convert 3 Letter Month to Number Excel

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 shortcuts Ctrl+C and Ctrl+V to copy and paste.

At last, you will get the converted Months in number format.

Convert 3 Letter Month to Number Excel


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.

Convert 3 Letter Month to Number Excel

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

Convert 3 Letter Month to Number Excel


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.

Convert 3 Letter Month to Number Excel

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

Convert 3 Letter Month to Number Excel

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.

Convert 3 Letter Month to Number Excel

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

Convert 3 Letter Month to Number Excel

Read More: How to Convert Date to Week Number of Month in Excel (5 Ways)


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

Convert 3 Letter Month to Number Excel

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.

Convert 3 Letter Month to Number Excel


Conclusion

I hope you found this article helpful. Here, I have explained 8 methods of How to Convert 3 Letter Month to Number Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo