Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Splitting Information into Rows.

Splitting Information into Rows

by Allen Wyatt
(last updated December 14, 2013)

2

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, 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 CellSplitter1()
    Dim Temp As Variant
    Dim CText As String
    Dim J As Integer
    Dim K As Integer
    Dim L As Integer
    Dim iColumn As Integer
    Dim lNumCols As Long
    Dim lNumRows As Long

    iColumn = 4

    Set wksSource = ActiveSheet
    Set wksNew = Worksheets.Add

    iTargetRow = 0
    With wksSource
        lNumCols = .Range("IV1").End(xlToLeft).Column
        lNumRows = .Range("A65536").End(xlUp).Row
        For J = 1 To lNumRows
            CText = .Cells(J, iColumn).Value
            Temp = Split(CText, Chr(10))
            For K = 0 To UBound(Temp)
                iTargetRow = iTargetRow + 1
                For L = 1 to lNumCols
                    If L <> iColumn Then
                        wksNew.Cells(iTargetRow, L) _
                          = .Cells(J, L)
                    Else
                        wksNew.Cells(iTargetRow, 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.

The macro relies upon the use of the Split function to tear apart the multi-line cells. This function is only available beginning in Excel 2000, and isn't available in Excel for the Mac at all. In addition, you might want to only run the macro on a particular selection of cells. To overcome all these potential problems, you will want to consider the following macro, instead:

Sub CellSplitter2()
    Dim iSplitCol As Integer
    Dim iEnd As Integer
    Dim sTemp As String
    Dim iCount As Integer
    Dim i As Integer
    Dim wksNew As Worksheet
    Dim wksSource As Worksheet
    Dim lRow As Long
    Dim lRowNew As Long
    Dim lRows As Long
    Dim lRowOffset As Long
    Dim iTargetRows As Integer
    Dim iCol As Integer
    Dim iCols As Integer
    Dim iColOffset As Integer
    Dim AWF As WorksheetFunction

    On Error GoTo ErrRoutine
    Application.ScreenUpdating = False

    'Set Column to split
    iSplitCol = 4

    iCols = Selection.Columns.Count
    lRows = Selection.Rows.Count

    iColOffset = Selection.Column - 1
    lRowOffset = Selection.Row - 1
    lRowNew = lRowOffset
    
    Set wksSource = ActiveSheet
    Set wksNew = Worksheets.Add

    Set AWF = Application.WorksheetFunction
    With wksSource
        For lRow = (lRowOffset + 1) To (lRowOffset + lRows)
            sTemp = .Cells(lRow, iSplitCol)
            If Right(sTemp, 1) <> vbLf Then
                sTemp = sTemp & vbLf
            End If
            iCount = (Len(sTemp) - _
              Len(AWF.Substitute(sTemp, vbLf, "")))

            For iTargetRows = 1 To iCount
                lRowNew = lRowNew + 1
                For i = (iColOffset + 1) To (iColOffset + iCols)
                    If i <> iSplitCol Then
                        wksNew.Cells(lRowNew, i) _
                          = .Cells(lRow, i)
                    Else
                        iEnd = InStr(sTemp, vbLf)
                        wksNew.Cells(lRowNew, i) _
                          = Left(sTemp, iEnd - 1)
                        sTemp = Mid(sTemp, iEnd + 1)
                    End If
                Next i
            Next iTargetRows
        Next lRow
    End With

ExitRoutine:
    Set wksSource = Nothing
    Set wksNew = Nothing
    Set AWF = Nothing
    Application.ScreenUpdating = True
    Exit Sub

ErrRoutine:
    MsgBox Err.Description, vbExclamation
    Resume ExitRoutine
End Sub

The macro still relies upon the use of a variable to indicate the column to be split apart. In this instance, the variable is iSplitCol, and it is set to column 4. The macro only works on the cells selected when it is first run, and the split-apart cells are transferred to a new worksheet. The address of the upper-left cell in the new worksheet is the same as the upper-left cell selected when the macro is run.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3263) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Splitting Information into Rows.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Setting Row Height in a Macro

Macros can be used to change the formatting of your worksheet, if desired. One change you might want to make is to the height ...

Discover More

Applying Table Formats

Want to make short work of formatting a large data table? You can use the AutoFormat feature of Excel to apply all sorts of ...

Discover More

Backing Up Custom Dictionaries

The custom dictionary used in Excel contains the information you decide relative to spelling. After a while, you might start ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (menu)

Removing a Macro from a Shortcut Key

When you assign a macro to a shortcut key, you make it easy to run the macro without ever removing your hands from the ...

Discover More

Forcing a Macro to Run when a Worksheet is Recalculated

Normally a macro is only calculated when you specifically tell Excel to calculate it. Some macros need to be calculated ...

Discover More

Deriving the Worksheet Name

Excel doesn't provide an easy way to grab the worksheet name for use within a worksheet. Here are some ideas on ways you can ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 9?

2014-12-15 04:16:30

Henry

Thank you so much for this. I inherited a spreadsheet with over 1000 rows. Each row contained a feature description and a cell that had multiple lines for each time the feature was requested by a user. It would have taken me days to manually separate each line to a new cell. Your code did it in seconds. Thanks again!


2014-10-14 01:33:13

Jarad

This is awesome. Thank you for this VBA macro. It will come in handy a lot for my needs.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.