Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Splitting Information into Rows.

Splitting Information into Rows

by Allen Wyatt
(last updated April 6, 2019)

3

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 CellSplitter()
    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 = 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, 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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9396) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel 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

Relative References when Recording Macros

When you record a macro, make sure that you know how Excel is recording your cell movements. This tip explains the ...

Discover More

Searching Comments

Need to find that misplaced comment in your worksheet? It's easy to do using the Find and Replace capabilities of Excel.

Discover More

Removing Spaces

Need to get rid of spaces in a range of cells? There are two ways you can approach the task, as described here.

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Getting Rid of Everything Except Numbers

Got some numbers and letters mixed up in the same cell? You may need to get rid of those letters so you are left with ...

Discover More

Pasting Excel Data within Word's Page Margins

The programs in the Microsoft Office suite are designed to work with each other easily. Sometimes there can be hiccups ...

Discover More

Limiting Number of Characters in a Cell

Need to limit the number of characters that can be entered into a cell? One easy way to do it is through the use of Data ...

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 8 + 3?

2019-06-17 19:21:22

Ed

I had to move:
lNumCols = Cells(1, Columns.Count).End(xlToLeft).Column
lNumRows = Cells(Rows.Count, 1).End(xlUp).Row
to just after
Set wksSource = ActiveSheet
for this to work in Excel 2013.

Sub CellSplitter()
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 = 1

Set wksSource = ActiveSheet
lNumCols = Cells(1, Columns.Count).End(xlToLeft).Column
lNumRows = Cells(Rows.Count, 1).End(xlUp).Row

Set wksNew = Worksheets.Add

iTargetRow = 0
With wksSource
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


2019-04-07 11:19:00

Willy Vanhaelen

In this tip's macro not only 3 variables are not declared (wksSource, wksNew, iTargetRow) which with Option Explicit causes the macro to crash but it also does a bad job. Although the splitting is done correctly the content of each cell in the row is repeated on each row that is added by this splitting which is absolutely unnecessary and very confusing.

My version (half size) deals with it. It only uses one loop:

Sub CelSplitter()
Dim iCol As Integer
Dim J As Integer
Dim lNumRows As Long
Dim Temp As Variant

iCol = 4

ActiveSheet.Copy after:=ActiveSheet
With ActiveSheet
lNumRows = Cells(.Rows.Count, iCol).End(xlUp).Row
Application.ScreenUpdating = False
For J = lNumRows To 1 Step -1
If InStr(.Cells(J, iCol), Chr(10)) Then
Temp = Split(.Cells(J, iCol), Chr(10))
.Range(.Cells(J + 1, iCol), .Cells(J + UBound(Temp), iCol)).EntireRow.Insert
.Range(.Cells(J, iCol), .Cells(J + UBound(Temp), iCol)) = Application.Transpose(Temp)
End If
Next
Application.ScreenUpdating = True
End With
End Sub


2019-04-06 07:17:06

Subodh Joshi

Split into columns (use Other = Control+J to indicate newline as separator), copy and then use 'transpose' when pasting. With 16,000 columns available, limitation will be memory considerations when copy/pasting rather than maximum number of newlines in a cell data. If needed, you can do it in 100K line chunks. Some manual work but no need for macro.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu 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.