Using Excel VBA Convert Month Name into Number, Month Number into Name

How to convert month name to number in Excel?

Sometimes, you may want to convert the month name to number or the number to month name, such as

In Excel, you can use formulas and VBA to quickly convert between month name and number.

Convert month name to number in Excel

Convert number to month name in Excel

Convert date to month name or month number with Kutools for Excel

How to be more efficient and save time when using Excel?

---

There are two ways that can help you to convert month names to numbers in Excel.

Type this formula =MONTH(DATEVALUE(A1&" 1")) ( A1 indicates the cell that you want to convert the month name to number, you can change it as you need) into a blank cell, and press Enter key. See screenshot:Method 1: Convert month name to number with formula.

If you want to convert a column list of month names to numbers, just drag the fill handle of the formula cell to fill the range you need. See screenshot:

Method 2: Convert month name to number with VBA

1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.

2. Click Insert > Module, and copy the VBA into the module.

VBA: Convert month name to number

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Sub ChangeNum() Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "Converter" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng     If Rng.Value <> "" Then         Rng.Value = Month(DateValue("03/" & Rng.Value & "/2014"))     End If Next End Sub

3. Click Run to run the VBA code, and a toolsforExcel dialog pops up for you to select a range of cells with month names that you want to convert to numbers. See screenshot:

4. Click OK, the month names in the range have been converted to numbers. See screenshot:

Tip: Using the above VBA may lose your original data, you can save them before you running the VBA code.

Method 1: Convert number to month name with formula.

Type this formula =TEXT(DATE(2000,A1,1),"mmmm") ( A1 indicates the cell that you want to convert the number to month name, you can change it as you need) into a blank cell, and press Enter key. See screenshot:

If you want to convert a column list of numbers to month names, just drag the fill handle of the formula cell to fill the range you need. See screenshot:

Tip: If you want to convert number to the abbreviation of the month name, you can use this formula =TEXT(DATE(2000,A1,1),"mmm").

Method 2: Convert number to month name with VBA

1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.

2. Click Insert > Module, and copy the VBA into the module.

VBA: Convert number to month name

1 2 3 4 5 6 7 8 9 10 11 12

Sub ChangeMonth() Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "Converter" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng     Rng.Value = VBA.Format(Rng.Value * 29, "mmmm") Next End Sub

3. Click Run to run the VBA code, and a KutoolsforExcel dialog pops up for you to select a range of cells with numbers you want to convert to the month names. See screenshot:

4. Click OK, the selected numbers in the range have been converted to month names. See screenshot:

Below is the video Tutorial.