Excel VBA需求：按整词拆分A列至B（40字符内）、C列

Got it, let's solve this problem efficiently. You need to split text in column A into two columns while keeping whole words intact, and automate this across 30+ worksheets. Here's a VBA solution that does exactly what you're asking for:

Sub SplitTextByWholeWords()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim originalText As String
    Dim splitPos As Integer
    Dim maxBLength As Integer: maxBLength = 40
    Dim maxCLength As Integer: maxCLength = 800
    
    ' Loop through every worksheet in your workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Find the last row with data in column A to avoid empty rows
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        
        ' Process each row (start at row 2 assuming row 1 is headers; adjust if needed)
        For i = 2 To lastRow
            originalText = Trim(ws.Cells(i, "A").Value)
            
            ' Only process text that meets your 30-120 character requirement
            If Len(originalText) >= 30 And Len(originalText) <= 120 Then
                ' Find the last space within the first 40 characters to keep whole words
                splitPos = InStrRev(Left(originalText, maxBLength), " ")
                
                ' Edge case: if no space exists in the first 40 chars (long single word), split at 40
                If splitPos = 0 Then
                    splitPos = maxBLength
                End If
                
                ' Populate column B with the first segment (whole words, max 40 chars)
                ws.Cells(i, "B").Value = Left(originalText, splitPos)
                
                ' Populate column C with remaining text (max 800 chars, though your source is shorter)
                ws.Cells(i, "C").Value = Trim(Mid(originalText, splitPos + 1))
                If Len(ws.Cells(i, "C").Value) > maxCLength Then
                    ws.Cells(i, "C").Value = Left(ws.Cells(i, "C").Value, maxCLength)
                End If
            Else
                ' For text outside 30-120 chars: put all in B, leave C empty
                ws.Cells(i, "B").Value = originalText
                ws.Cells(i, "C").Value = ""
            End If
        Next i
    Next ws
    
    MsgBox "Text splitting finished! Check columns B and C.", vbInformation
End Sub

How This Works #

• Worksheet Loop: Automatically processes every sheet in your workbook, so you don't have to run it manually for each of the 30 sheets.
• Whole Word Split: The InStrRev function finds the last space in the first 40 characters. This ensures words like "Fittings" won't be split mid-word—if the 40th character falls inside a word, we jump back to the previous space to keep the entire word in column C.
• Safety Edges: Handles cases where there's no space in the first 40 characters (a long single word) by splitting exactly at 40, and caps column C at 800 characters as requested.
• Header Friendly: Assumes row 1 is a header row—if your data starts at row 1, just change For i = 2 To lastRow to For i = 1 To lastRow.

How to Use the Code #

1. Open your Excel workbook.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the Project Explorer > Insert > Module.
4. Paste the code into the new module.
5. Press F5 to run the macro, or assign it to a button on your worksheet for quick access later.

Notes #

• Backup First: Always make a copy of your workbook before running macros, just to be safe.
• Adjust Limits: If you need to change the max length for columns B or C, modify the maxBLength and maxCLength variables at the top of the code.

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