Excel-VBA：多列含字符串与数字的数据合并为指定格式列需求

Got it, let's build this VBA macro exactly how you need it. Here's a step-by-step breakdown plus the full code:

Core Logic Breakdown #

First, let's map out what we need to do for each row:

• Split column A into the name (letters) and number parts
• Format the number to 3 digits with leading zeros (e.g., 25 → 025)
• Combine the formatted name+number with column C (if it has a value)
• Append column B's code with the required ": " separator

Full VBA Code #

Sub GenerateFormattedColumnD()
    Dim targetSheet As Worksheet
    Dim lastDataRow As Long
    Dim rowIndex As Long
    Dim aCellValue As String
    Dim nameSegment As String
    Dim numberSegment As String
    Dim formattedNumber As String
    Dim finalDValue As String
    
    ' Set your target worksheet (replace "Sheet1" with your actual sheet name if needed)
    Set targetSheet = ThisWorkbook.Sheets("Sheet1")
    ' Find the last row with data in column A
    lastDataRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each data row (skip row 1 if it's a header)
    For rowIndex = 2 To lastDataRow
        aCellValue = targetSheet.Cells(rowIndex, "A").Value
        nameSegment = ""
        numberSegment = ""
        
        ' Split column A into letters (name) and digits (number)
        For charIndex = 1 To Len(aCellValue)
            currentChar = Mid(aCellValue, charIndex, 1)
            If currentChar Like "[A-Za-z]" Then
                nameSegment = nameSegment & currentChar
            ElseIf currentChar Like "[0-9]" Then
                numberSegment = numberSegment & currentChar
            End If
        Next charIndex
        
        ' Format number to 3 digits with leading zeros
        formattedNumber = Format(numberSegment, "000")
        
        ' Build the final string for column D
        finalDValue = nameSegment & formattedNumber
        ' Add column C content only if it's not empty
        If targetSheet.Cells(rowIndex, "C").Value <> "" Then
            finalDValue = finalDValue & " " & targetSheet.Cells(rowIndex, "C").Value
        End If
        ' Append column B's code
        finalDValue = finalDValue & ": " & targetSheet.Cells(rowIndex, "B").Value
        
        ' Write the result to column D
        targetSheet.Cells(rowIndex, "D").Value = finalDValue
    Next rowIndex
    
    MsgBox "Column D has been generated successfully!", vbInformation
End Sub

How to Use This Macro #

1. Open your Excel file
2. Press Alt + F11 to open the VBA Editor
3. Right-click your workbook in the Project Explorer → Insert → Module
4. Paste the code above into the module window
5. Adjust the targetSheet line if your data is on a sheet other than "Sheet1"
6. Press F5 to run the macro, or go back to Excel and use Alt + F8 to select and run GenerateFormattedColumnD

Key Notes #

• This code handles cases where column C is empty (it just skips adding that part)
• The number formatting ensures your number is always 3 digits (e.g., 5 → 005, 12 → 012, 456 → 456)
• It safely stops at the last row with data in column A, so you don't waste time looping empty rows

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