How to Split Cell Content into Multiple Rows in Excel (Scenarios + Tools Comparison)

Tip: You can change the delimiter "," to ";" or "|" depending on how your data is separated.

Step 2. Press the Enter key.

Method 3: Power Query – Split and Expand Rows (Microsoft 2016 and later versions)

Power Query is ideal for structured, repeatable operations. It’s powerful, refreshing, and great for batch data transformations.

Suited for: Both of Scenario 1 (Split Only the Target Cell), and Scenario 2 (Split Cell and Keep Other Column Data)

Steps: Take Scenario 1 (Split Only the Target Cell) as an Example

Step 1. Select your range, go to Data > Get & Transform > From Table/Range.

an 

Step 2. In the Create Table, click OK. (If your selected range has headers, tick My table has headers.)

Step 3. In the Power Query Editor, use Split Column by Delimiter.

Step 4. Select or enter the delimiter, and then click Advanced to expand the options, select Split into Rows, then click OK.

Step 5. Select Close & Load > Close & Load or Close & Load To.

The same steps apply to Scenario 2 (Splitting Cells While Preserving Other Column Data) - let's examine the results.

The target range of cells:

Result:

Note: In the Power Query Editor window, select only the target column that you want to split into rows, then click By Delimiter under Split Column.

Tip: Add Trim and Clean steps to fix whitespace or special characters.

Method 4: Kutools Plugin – Clicks for Splitting

If you frequently split cells and prefer a GUI-based solution, Kutools is an excellent tool. It simplifies complex operations into a few clicks.

Suited for: Both of Scenario 1 (Split Only the Target Cell), and Scenario 2 (Split Cell and Keep Other Column Data)

Steps: Scenario 1 (Split Only the Target Cell)

Step 1. Select the target cell.

Step 2. Go to Kutools > Merge & Split > Split Cells.

Step 3. Choose Split into Rows and define the delimiter. Click OK./p>

Step 4. Select a destination cell. Click OK.

Steps: Scenario 2 (Split Cell and Keep Other Column Data)

Step 1. Select the column you want to split to rows.

Step 2. Go to Kutools > Merge & Split > Split Data to Rows.

Step 3. Define the delimiter. Click OK.

The same steps apply to Scenario 2 (Splitting Cells While Preserving Other Column Data) - let's examine the results.

The target range of cells:

Result:

Note: The 30-day Trial version offers all functionality for testing.

Method 5: VBA Script – Full Custom Control

For users with programming knowledge, VBA provides ultimate flexibility. You can tailor scripts to match your exact data layout and business rules.

Suited for: Both of Scenario 1 (Split Only the Target Cell), and Scenario 2 (Split Cell and Keep Other Column Data)

Steps:

Step 1. Press Alt + F11 to open the VBA editor.

Step 2. Click Insert > Module.

Step 3. Copy and paste the VBA below.

Scenario 1 Split Only the Target Cell

'Update by Extendoffice
Sub SplitCellToRows_Simple()
    Dim InputCell As Range
    Dim Values As Variant
    Dim i As Long

  
    Set InputCell = Range("A1") ' change A1 to your target cell

    Values = Split(InputCell.Value, ",") 
    For i = 0 To UBound(Values)
        InputCell.Offset(i, 1).Value = Trim(Values(i))
    Next i
End Sub

Scenario 2 Split Cell and Keep Other Column Data

'Update by Extendoffice
Sub SplitRowsWithOtherColumns()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim arr As Variant, j As Long
    Dim SplitVals As Variant
    Dim CurrentRow As Long

    Set ws = ActiveSheet
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = LastRow To 2 Step -1
        If InStr(ws.Cells(i, 2).Value, ",") > 0 Then
            SplitVals = Split(ws.Cells(i, 2).Value, ",")

            For j = UBound(SplitVals) To 1 Step -1
                ws.Rows(i + 1).Insert Shift:=xlDown
                ws.Cells(i + 1, 1).Value = ws.Cells(i, 1).Value
                ws.Cells(i + 1, 2).Value = Trim(SplitVals(j))
            Next j

            ws.Cells(i, 2).Value = Trim(SplitVals(0))
        End If
    Next i
End Sub

Note: This VBA script is configured to work specifically when your data is located in columns A to B and the values to be split are in column B.

💡Tips:

• Always back up your data before running a macro.
• Replace "," in the script with your actual delimiter (e.g., ";" for semicolons or "|" for pipe characters) to match the format of your data.

Common Issues

• Blank rows or columns: Usually caused by trailing delimiters or hidden spaces.
• Merged cell errors: Unmerge cells before starting.
• Power Query not refreshing: Remember to click Refresh after modifying source data.
• Wrong delimiter: A semicolon vs comma mistake can throw off entire results.

Relative Articles:

• Transpose Range utility: Easily convert multiple rows to columns and rows in Excel
• Unit Converter tool: Quickly convert decimal number to binary/octal/hex number or vice versa