Excel单元格内多行值拆分至独立行并保留其他列数据

Hey there, I’ve helped a lot of folks solve this exact problem—splitting cells with line-break separated values into individual rows while keeping all other column data intact. Here are three solid, non-manual methods you can use depending on your comfort level with Excel tools:

This is the easiest, most robust method that doesn’t require coding. It works for Excel 2016+, Excel 365, and Excel Online.

• Select your entire data range (including headers).
• Go to the Data tab > Click From Table/Range (Excel will convert your range to a table if it isn’t already—just confirm the prompt).
• In the Power Query Editor, select the column with line-separated values.
• Go to the Transform tab > Click Split Column > Choose By Delimiter.
• In the split dialog:
  • Select Custom as the delimiter, then press Ctrl + J (this inserts the invisible line break character that Excel uses).
  • Choose Into Rows under "Split into".
• Click OK, then go to the Home tab > Click Close & Load to export the cleaned data back to a new sheet in your workbook.

If you need a reusable solution that runs directly in your sheet, a VBA script is perfect.

• Press Alt + F11 to open the VBA Editor.
• Right-click your workbook in the Project Explorer pane > Insert > Module.
• Paste the following code into the module:

Sub SplitLineBreakCellsToRows()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long, valCount As Long
    Dim splitValues As Variant
    
    ' Set the active sheet (change to specific sheet name if needed, e.g., Sheet1)
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Column A = your unique identifier column
    
    ' Loop from bottom to top to avoid row shifting issues
    For i = lastRow To 2 Step -1
        ' Split the cell value by line breaks (vbLf = line feed character)
        splitValues = Split(ws.Cells(i, "B").Value, vbLf) ' Column B = your multi-value column
        valCount = UBound(splitValues)
        
        If valCount > 0 Then
            ' Insert rows for each extra value
            ws.Rows(i + 1 To i + valCount).Insert
            ' Copy all other column data to the new rows
            ws.Rows(i).Copy ws.Rows(i + 1 To i + valCount)
            ' Assign each split value to its own row
            For valCount = 0 To UBound(splitValues)
                ws.Cells(i + valCount, "B").Value = splitValues(valCount)
            Next valCount
        End If
    Next i
End Sub

• Quick tweaks before running:
  • Replace "A" with the column letter that has your unique data (to calculate the last row correctly).
  • Replace "B" with the column containing your line-separated values.
• Press F5 to run the script, or assign it to a button on your sheet for one-click access later.

If you prefer sticking to formulas (works for all Excel versions), here’s how to do it:
Let’s assume your data starts at A1, with multi-values in column B.

1. Add a helper column (e.g., column C) to count values per cell:
   • In C1, enter: =LEN(B1)-LEN(SUBSTITUTE(B1,CHAR(10),""))+1
   • Drag this formula down to all rows in your data. This counts how many line-separated values are in each cell.
2. Extract the split rows:
   • For your first data column (A), in a new column (e.g., E1), enter this array formula (use Ctrl + Shift + Enter in pre-365 Excel; just Enter in 365/2021):
     =INDEX($A$1:$A$10,AGGREGATE(15,6,ROW($A$1:$A$10)/($C$1:$C$10>=ROW(INDIRECT("1:"&SUM($C$1:$C$10)))),ROWS($E$1:E1)))
   • For the split values column (B), in F1, enter:
     =TRIM(MID(SUBSTITUTE(TEXTJOIN(CHAR(10),TRUE,$B$1:$B$10),CHAR(10),REPT(" ",99)),(ROWS($F$1:F1)-1)*99+1,99))
3. Drag both formulas down until you see #N/A errors—this means you’ve extracted all values.

• Note: Adjust the range references ($A$1:$A$10, $B$1:$B$10) to match your actual data range.

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