Excel VBA：利用Concatenate函数合并年月列并格式化

Got it, let's break down how to solve this. You have month numbers (1-12) in column B, two-digit year codes (like 8 for 2008, 10 for 2010) in column C, and want to merge them into column E as formatted strings like "Jan 2008". Here are two solid approaches, including one that uses Concatenate as you mentioned:

Method 1: Use DateSerial for Reliable Date Formatting (Recommended) #

This method leverages Excel's built-in date handling to avoid string-related errors. We'll create a valid date first, then format it to your desired style:

Sub FormatMonthYearToText()
    Dim targetSheet As Worksheet
    Dim lastDataRow As Long
    Dim currentRow As Long
    
    ' Update this to your actual worksheet name
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
    
    ' Find the last row with data in column B
    lastDataRow = targetSheet.Cells(targetSheet.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through each row (start at 2 if row 1 is headers)
    For currentRow = 2 To lastDataRow
        ' Convert two-digit year to full four-digit year
        Dim fullYear As Integer
        ' Adjust this logic if your years span beyond 2029
        fullYear = IIf(targetSheet.Cells(currentRow, "C").Value <= 29, _
                       2000 + targetSheet.Cells(currentRow, "C").Value, _
                       1900 + targetSheet.Cells(currentRow, "C").Value)
        
        ' Generate date and format to "mmm YYYY"
        targetSheet.Cells(currentRow, "E").Value = _
            Format(DateSerial(fullYear, targetSheet.Cells(currentRow, "B").Value, 1), "mmm YYYY")
    Next currentRow
End Sub

Method 2: Use Concatenate (As Requested) #

If you specifically want to use the Concatenate function, this approach builds the string by combining a formatted month and the full year:

Sub ConcatenateMonthYear()
    Dim targetSheet As Worksheet
    Dim lastDataRow As Long
    Dim currentRow As Long
    
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
    lastDataRow = targetSheet.Cells(targetSheet.Rows.Count, "B").End(xlUp).Row
    
    For currentRow = 2 To lastDataRow
        Dim fullYear As Integer
        fullYear = IIf(targetSheet.Cells(currentRow, "C").Value <= 29, _
                       2000 + targetSheet.Cells(currentRow, "C").Value, _
                       1900 + targetSheet.Cells(currentRow, "C").Value)
        
        ' Use Concatenate formula to build the string
        targetSheet.Cells(currentRow, "E").Formula = _
            "=CONCATENATE(TEXT(B" & currentRow & ",""mmm""), "" "", " & fullYear & ")"
        
        ' Optional: Convert formula to static text if you don't need the formula to update
        ' targetSheet.Cells(currentRow, "E").Value = targetSheet.Cells(currentRow, "E").Value
    Next currentRow
End Sub

Key Notes: #

• Year Conversion Logic: The IIf statement assumes years ≤29 are 2000s (e.g., 8→2008) and years >29 are 1900s. If all your years are post-2000, simplify to fullYear = 2000 + targetSheet.Cells(currentRow, "C").Value.
• Worksheet Name: Make sure to replace Sheet1 with your actual worksheet's name.
• Static vs Formula: In Method 2, uncomment the optional line if you want static text instead of a dynamic formula.

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