Splitting Information into Rows

James has some data in a worksheet that is contained in a series of rows. One of the columns in the data includes cells that have multiple lines per cell. (The data in the cell was separated into lines by pressing Alt+Enter between items.) James would like to split this data into multiple rows. For instance, if there were three lines of data in a single cell in the row, then the data in that cell should be split out into three rows.

Excel provides a handy way to split data into separate columns using the Text to Columns tool. This can be used to split the data based on the presence of the ASCII 10 character (linefeed), which is what Excel inserts when you press Alt+Enter. The problem is that while this successfully splits the data into separate columns, it doesn't get it into separate rows, like James requested.

That means that the solution to this problem must include the use of a macro. One approach is shown in the following code. In this example, the macro assumes that you want to "expand" everything in the worksheet, and that the data in the worksheet starts in row 1.

Sub CellSplitter()
    Dim Temp As Variant
    Dim CText As String
    Dim J As Long
    Dim K As Integer
    Dim L As Long
    Dim iColumn As Integer
    Dim lTargetRow As Long
    Dim lNumCols As Long
    Dim lNumRows As Long
    Dim wksSource As Worksheet
    Dim wksNew As Worksheet

    iColumn = 4

    Set wksSource = ActiveSheet
    Set wksNew = Worksheets.Add

    lTargetRow = 0
    With wksSource
        lNumCols = Cells(1,Columns.Count).End(xlToLeft).Column
        lNumRows = Cells(Rows.Count,1).End(xlUp).Row
        For J = 1 To lNumRows
            CText = .Cells(J, iColumn).Value
            Temp = Split(CText, vbLf)
            For K = 0 To UBound(Temp)
                lTargetRow = lTargetRow + 1
                For L = 1 to lNumCols
                    If L <> iColumn Then
                        wksNew.Cells(lTargetRow, L) = .Cells(J, L)
                    Else
                        wksNew.Cells(lTargetRow, L) = Temp(K)
                    End If
                Next L
            Next K
        Next J
    End With
End Sub

Note that in order to run the macro, you will need to specify, using the iColumn variable, the column that contains the cells to be split apart. As written here, the macro splits apart info in the fourth column. In addition, the split-apart versions of the cells are stored in a new worksheet, so that the original worksheet is not affected at all.

There is one thing to keep in mind if you choose to use this macro. If you are working with a workbook that was created in an old version of Excel on the Mac (Excel 2001 or older), then the above won't work. That's because those earlier versions of Excel on the Mac didn't use linefeeds (ASCII 10), but instead used carriage returns (ASCII 13). In that case, you can use the above macro, provided you change the Split function to use vbCr instead of vbLf.

Finally, if the macro doesn't seem to work for you, make sure that the cells in your worksheet actual contain linefeeds (or carriage returns). If you have text wrapping turned on in your cells, then Excel automatically wraps text from line to line within the cell. This does not mean, however, that the cells contain any linefeed (or carriage return) characters. Since they aren't there, the macro cannot break anything out into separate rows.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9396) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Splitting Information into Rows.