Split Data Into Rows or Columns in Excel

When one cell holds several values, such as Kochi, Chennai, Delhi, you can split them into separate columns or rows. Excel 365 does it with one formula; for other versions, these macros do it in a click.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

With a formula (Excel 365)

TEXTSPLIT splits text by any delimiter:

To Formula
Split into columns =TEXTSPLIT(A2,", ")
Split into rows =TEXTSPLIT(A2,,", ")
Split a whole column into rows =TEXTSPLIT(TEXTJOIN(",",TRUE,A2:A20),,",")

Macro: split the active cell into rows

Select a cell such as Kochi, Chennai, Delhi and run. The parts are written down the next column:

Sub SplitIntoRows()
    Dim parts, i As Long
    parts = Split(ActiveCell.Value, ",")
    For i = 0 To UBound(parts)
        ActiveCell.Offset(i, 1).Value = Trim(parts(i))
    Next i
End Sub

Macro: split the selection into columns

Uses Excel’s Text to Columns on the selected cells, splitting at each comma:

Sub SplitIntoColumns()
    Selection.TextToColumns Destination:=Selection.Cells(1), _
        DataType:=xlDelimited, Comma:=True, Space:=False
End Sub

The parts overwrite the cells to the right, so make sure those columns are empty. For a different delimiter, use Other:=True, OtherChar:="|".

Without a macro

  1. Select the cells and go to Data → Text to Columns.
  2. Choose Delimited, tick the delimiter, such as Comma, and click Finish.

For splitting into rows, Power Query’s Split Column → By Delimiter → Advanced → Rows is the most reliable no-code method.

Things to know

  • Trim removes the space after each comma.
  • Splitting overwrites existing data in the target cells. Save a copy first.

Related macros