Excel中多内容字符串修剪及含内部空格的列拆分问题

Got it, let's work through this Excel data splitting challenge. The core issue here is that standard space-based splitting (like Text to Columns) breaks when some columns have internal spaces—but since your columns follow a fixed order, we can use targeted workarounds based on that structure.

1. Excel 365/2021: Use TEXTSPLIT with Targeted Substitution #

If you have access to newer dynamic array functions, this is the fastest method. The trick is to replace only the column-separating spaces (not internal ones) with a unique delimiter, then split on that.

For example, let's say you need to split into 4 columns, where:

• Column 1 = ID (no internal spaces)
• Columns 2 & 3 = text with internal spaces
• Column 4 = numeric value (no internal spaces)

Use this formula (adjust substitution counts based on your column count):

=TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A1," ","|",2)," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),"|")

Breakdown: #

• SUBSTITUTE(A1," ","|",2): Replaces the 2nd space (separator between Column 1 and 2) with |
• SUBSTITUTE(..., " ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1): Replaces the 2nd-to-last space (separator between Column 3 and 4) with |
• TEXTSPLIT(..., "|"): Splits the modified string into columns using | as the delimiter

2. All Excel Versions: Power Query (Visual, Batch-Friendly) #

Power Query is great for bulk data and works in all modern Excel versions. It lets you define custom splitting logic without messy formulas.

Step-by-Step: #

1. Select your data range → Go to the Data tab → Click From Table/Range (check "My table has headers" if applicable)
2. In the Power Query Editor, add a custom column to identify all space positions:

   List.PositionOf(Text.ToList([YourColumnName]), " ", Occurrence.All)
   

3. Add another custom column to extract each column based on fixed order (example for 4 columns: ID, Name, Department, Amount):

   let
       str = [YourColumnName],
       spaces = [SpacePositions],
       col1 = Text.Range(str, 0, spaces{0}),
       col4 = Text.Range(str, spaces{List.Count(spaces)-1}+1),
       middle = Text.Range(str, spaces{0}+1, spaces{List.Count(spaces)-1}-spaces{0}-1),
       lastMiddleSpace = Text.PositionOf(middle, " ", Occurrence.Last),
       col2 = Text.Range(middle, 0, lastMiddleSpace),
       col3 = Text.Range(middle, lastMiddleSpace+1)
   in
       {col1, col2, col3, col4}
   

4. Expand the custom column into separate columns → Click Close & Load to bring the split data back to Excel.

3. Legacy Excel/Automation: VBA Script #

If you're on an older Excel version or need to automate the process repeatedly, a VBA script can handle the logic programmatically.

Full Script: #

Sub SplitFixedColumnsWithInternalSpaces()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim str As String
    Dim spacePositions As Variant
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    'Loop through each row (skip header row if row 1 is headers)
    For i = 2 To lastRow
        str = ws.Cells(i, "A").Value
        spacePositions = GetSpacePositions(str)
        
        'Extract columns based on fixed order (adjust for your column count)
        Dim col1 As String, col2 As String, col3 As String, col4 As String
        col1 = Left(str, spacePositions(0) - 1)
        col4 = Right(str, Len(str) - spacePositions(UBound(spacePositions)))
        
        Dim middleStr As String
        middleStr = Mid(str, spacePositions(0) + 1, spacePositions(UBound(spacePositions)) - spacePositions(0) - 1)
        Dim lastSpaceInMiddle As Integer
        lastSpaceInMiddle = InStrRev(middleStr, " ")
        col2 = Left(middleStr, lastSpaceInMiddle - 1)
        col3 = Right(middleStr, Len(middleStr) - lastSpaceInMiddle)
        
        'Write results to columns B-E
        ws.Cells(i, "B").Value = col1
        ws.Cells(i, "C").Value = col2
        ws.Cells(i, "D").Value = col3
        ws.Cells(i, "E").Value = col4
    Next i
End Sub

'Helper function to get all space positions in a string
Function GetSpacePositions(str As String) As Variant
    Dim positions As Collection
    Dim pos As Integer
    Set positions = New Collection
    
    pos = InStr(str, " ")
    Do While pos > 0
        positions.Add pos
        pos = InStr(pos + 1, str, " ")
    Loop
    
    'Convert collection to array
    Dim arr() As Integer
    ReDim arr(0 To positions.Count - 1)
    For i = 0 To positions.Count - 1
        arr(i) = positions(i + 1)
    Next i
    GetSpacePositions = arr
End Function

How to Use: #

1. Press Alt + F11 to open the VBA Editor
2. Insert a new module → Paste the script
3. Adjust column references (e.g., A for source, B-E for output) to match your data
4. Run the SplitFixedColumnsWithInternalSpaces macro

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