如何用VBA从非日期格式的独立月份列提取1-12的数字？

根据你G列的月份文本格式，选择对应的VBA代码即可：

情况1：G列是英文月份名（全称/缩写，如"January"、"Feb"） #

利用DateValue将月份名转换为日期后提取数字：

Sub ExtractMonthFromEnglish()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    Set ws = ActiveSheet ' 可指定工作表，如Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row ' 获取G列最后一行数据
    
    For i = 2 To lastRow
        ' 拼接成完整日期格式后提取月份
        ws.Cells(i, "H").Value = Month(DateValue("1 " & ws.Cells(i, "G").Value & " 2024"))
    Next i
End Sub

情况2：G列是中文月份名（如"一月"、"十二月"） #

通过匹配预设的中文月份数组获取对应数字：

Sub ExtractMonthFromChinese()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim monthNames As Variant
    Dim monthNum As Integer
    
    monthNames = Array("一月", "二月", "三月", "四月", "五月", "六月", _
                      "七月", "八月", "九月", "十月", "十一月", "十二月")
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
    
    For i = 2 To lastRow
        For monthNum = 0 To 11
            If ws.Cells(i, "G").Value = monthNames(monthNum) Then
                ws.Cells(i, "H").Value = monthNum + 1
                Exit For
            End If
        Next monthNum
    Next i
End Sub

情况3：G列是带数字的文本（如"3月"、"08月"、"Month 5"） #

直接提取文本中的数字部分：

Sub ExtractMonthFromNumberText()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cellText As String
    Dim numStr As String
    Dim char As String
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
    
    For i = 2 To lastRow
        cellText = ws.Cells(i, "G").Value
        numStr = ""
        ' 遍历字符提取数字
        For Each char In Split(cellText, "")
            If IsNumeric(char) Then
                numStr = numStr & char
            End If
        Next char
        ' 转换为数字并写入H列
        If numStr <> "" Then
            ws.Cells(i, "H").Value = CLng(numStr)
        End If
    Next i
End Sub

使用步骤 #

1. 按Alt + F11打开VBA编辑器
2. 右键左侧工程窗口中的目标工作表，选择「插入」→「模块」
3. 粘贴对应情况的代码，按F5运行即可，结果会写入H列（可自行修改代码中的"H"调整输出列）

内容的提问来源于stack exchange，提问作者Sam