Convert month name to number
How to convert a month's name into a number using an Excel and VBA method
Example: Convert month name to number
|
=MONTH(1&B5)
|
The formula returns the number of the specified month. Inserting 1 in front of the month name, through the use of the & symbol, allows Excel to recognise the month as a date. This concatenation is captured in the Excel MONTH function to convert the month, represented by the date, into a numeric value.
Note: to apply this formula against all of the month names, as per the image above, you will need to drag (apply) the formula across all of the rows from row 5 to row 16. |
Dim ws As Worksheet
ws.Range("C5") = Month(DateValue("01/" & ws.Range("B5").Value & "/2017"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Month Name: If using this exact VBA code, which sources the month name from cell ("B5"), you will need to capture the month in cell ("B5").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Month Name: Select the month which you want to convert to a number by changing the cell reference ("B5") to any cell in the worksheet that contains the date and doesn't conflict with the formula.
METHOD 2. Convert month name to number with a For Loop
VBA
Dim ws As Worksheet
For x = 5 To 16
ws.Range("C" & x) = Month(DateValue("01/" & ws.Range("B" & x).Value & "/2017"))
Next x
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Month Name Rage: In this example we are converting month names in range ("B5:B16") into a numeric value. Therefore, if you are using this exact VBA code you will need to capture all the month names in range ("B5:B16") that you want to convert into a numeric value.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Month Name Rage: Select the range that captures the month names that you want to convert into a number by changing the range reference ("B5:B16") in the VBA code to any range in the worksheet that doesn't conflict with the formula. If you change the number or location of rows then you will need to change the parameters that is driving the For Loop. In this case its the values that we have nominated for x, which are from 5 to 16.
This tutorial provides instructions on how to convert a month's name into a number using an Excel and VBA method.
Excel Method: Using an Excel method, we need to apply the Excel MONTH function and represent the month name as a date.
VBA Method: Using a VBA method we need to apply a combination of Month and DateValue functions. The Month function in the VBA code performs the same function as the one written in an Excel cell. The DateValue function is used to convert the month name into a date, to which we have to assign a day and year. In this example we allocated the first day of the month with a 2017 year.
This tutorial provides two VBA methods. The first method converts a single month name into a number. The second method converts a range of month names into numbers.
=MONTH(1&month_name)
ARGUMENTS
month_name: The name of the month you want to convert to a number.