Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. 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 August 3, 2016)

30

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 = .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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9396) applies to Microsoft Excel 2007 and 2010. 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

Inserting the Current Month

Need to add the name of the current month to your document? Word includes a field that can make the addition easy, and it ...

Discover More

Adding Column Headings to a Table of Contents

Word makes it easy to create a Table of Contents. If you want column headings in that table, getting them takes a bit of ...

Discover More

Printing a Test Page

Windows includes the ability to print a test page to a printer. This is a good idea when you want to check whether the ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Quickly Entering Dates and Times

Excel provides keyboard shortcuts for a variety of purposes. This tip examines two such shortcuts, designed to allow entering ...

Discover More

Displaying an Input Format in a Cell

Want to show a user, in a cell, what you expect their input to look like? Unfortuantely it cannot be done natively in Excel. ...

Discover More

Copying Cells to Fill a Range

Excel provides two really helpful shortcuts you can use to fill a range of cells, either horizontally or vertically. These ...

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 8Mpixels. 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 6 - 3?

2017-03-22 12:06:58

Wenger786

Alternatively:

(in Excel 2016)

1. Highlight the cell
2. Hit "Text to columns" option under the "Data" menu
3. Choose Delimited, Other, replacing the delimiter box text with +0010
4. hit "next" or "finish" after checking the data preview


2017-01-11 11:44:02

Matthew

This made a total mess of my formatting. The following worked much better for me since it doesn't create a new worksheet and thereby lose all formatting(modified from https://www.extendoffice.com/documents/excel/2211-excel-split-cell-by-carriage-return.html):

Sub SplitCells()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
lLFs = VBA.Len(Rng) - VBA.Len(VBA.Replace(Rng, vbLf, ""))
If lLFs > 0 Then
Rng.Offset(1, 0).Resize(lLFs).EntireRow.Insert
Rng.Resize(lLFs + 1).Value = Application.WorksheetFunction.Transpose(VBA.Split(Rng, vbLf))
End If
Next
End Sub


2017-01-06 16:28:51

Bob Johanson

I use this on large single cell tables constructed by several colleagues. It didn't work when I first tried it, but after a few changes, it worked as you described above:
1. Dim iTargetRow As Integer
2. Dim wksSource As Worksheet
3. Dim wksNew As Worksheet
4. Add wksSource.Select after
Set wksNew = Worksheets.Add
5. Place the top of the cell to change in Row 1.

Thanks, Allen
Bob


2016-12-19 11:26:08

Patricia

Dave, GREAT MACRO! I was able to use it. If I have column A to C with only 1 value in each cell, and then column D to E with multi-line data, how do I enable it so that when I run the macro, column A to C repeats itself down the column for the same number or rows as column D to E? Right now after I use your macro as is, column A to C don't repeat itself and are in just 1 row each. THANK YOU!


2016-11-14 20:43:31

dave tamblyn

Alan's macro is good if there is only one column with multi-line data. For the common situation where there are several consecutive columns with Alt-Entered data, it is convenient to prompt the user for first and last column and use a for next next loop. Also, remove the L loop and use iColumn in its place.

firstCol = InputBox("First column?", , 1)
lastCol = InputBox("Last column?", , 1)

Set wksSource = ActiveSheet
Set wksNew = Worksheets.Add

For iColumn = firstCol To lastCol

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
wksNew.Cells(iTargetRow, iColumn) = Temp(K)
Next K
Next J
End With
Next iColumn


2016-09-22 09:39:24

Shalabh Jain

I have a data in excel. It has number and dates and I want to split the data with 3 rows as blank when number is changed. or I have separate sheet where I have mentioned details with number and dates. some times number remains the same however date is changing. Please help me to split the data with 3 rows blanks accordingly.


2016-09-07 11:04:33

Jim Swindle

Surendera M. Bhanot, your method worked for me on a file with multiple colums of multi-row cells. Here are more detailed instructions:
1. Copy all of the data
2. Paste into Word
3. Format as narrow margins, landscape, 11 x 17, to fit the columns on one page.
4. Searched and replaced ^l with ^p (replaced a manual line break with a paragraph mark), replace all
5. Selected the pasted data in Word
6. Table, Convert text to table; left the number of columns at 1
7. Copied the data
8. Pasted into a new Excel file

Be sure to check to see whether things line up right. If your source file has blank spots or has rows that depend on vertical alignment within the cell, you may have to deal with those separately.


2016-08-19 11:36:48

Cindy

This was tremendously helpful. A huge thank you to you. I finally was able to look like a superstar at work where as of late I have been rating at the bottom of the barrel. Yet for the past 19 years I was at the top. I can't thank you enough. This came at the perfect timing and it work amazing. Exactly what I needed it to do. This would have taken hours to do manually


2016-08-03 09:58:28

Kevin M

I have used the following line of code for several years. Just replace the name in Quotes "Dup_File" to whatever you want to name the worksheet.

wksNew.Name = "Dup_File" ' changes name of new sheet to "Dup_File"

However, in the Tip given, you would then need to explicitly call the Sheet by the name given in the remaining lines of code. So wksNew.Cells(iTargetRow, L) = .Cells(J, L)
would become Sheets("Dup_File").Cells(iTargetRow, L) = .Cells(J, L)


2016-07-06 19:27:24

William V

Thanks a Lot man!!


2016-04-01 15:39:14

GregJ

Allen, great macro. Worked out of the box and does exactly what I was looking for. Great job.


2015-09-18 01:06:29

NightCrawler

Hi,
I loved the macro that you have provided. But how can we add more columns to be processes simultaneously.
eg. 4, 8 &6 have cells separated with Alt+Enter.


2015-08-04 13:25:19

Tammy

Thank You! Many good options/suggestions for getting this done out there, but most focus on comas or other special characters for seperation, your approach focus on what makes the cells problematic in the first palce Alt+Enter. did the trick for me.

thank you


2015-04-13 18:38:46

Mark

This tip is awesome!

Is there a way to modify this so that the new worksheet that is created always has the same name?

For example, let's say you wanted the new sheet's name to always be "Frank".


2015-03-11 09:09:56

Alessandro

Thanks Allen, your macro is exactly what i was looking for!
Cheers,
Alessandro


2015-03-09 10:40:32

Jiggy

James I have used a right click "split cell" command (or something to that effect) before that allowed you to split a single cell into rows or columns but for some reason it has been removed (at least in 2010). The current ribbon will allow you to add the split cell command to the ribbon from the 'customize the ribbon' function, but it does not work either. It was a lot simpler than macros, and using text to column delimiters. Will let you know if I find it.


2015-02-18 04:56:47

Alfa Man

A simple non macro solution:

Select and copy the column to the clipboard.

Past into a text editor like NotePad++ (free).

This will separate all contents into separate lines.

The rows that contained multiple lines(Alt-Enter lines) will appear with " prefixing at the first line and ending with " at the last line.
eg: For any multi-line (Alt-Enter) cells in Excel, such as the cell:
Row 1: textA
Row 2: textB <Alt-Enter>
textC <Alt-Enter>
textD <Alt-Enter>
Row 3: textE

Will appear as separate lines:
textA
"textB
textC
textD"
textE

Do a find and replace for "
to clean up/remove them if required.

Then select and past all your separated lines back into Excel.

Simple.


2014-07-23 06:16:26

MikeF

For simplicity you can select multiple Alt+Enter rows in the formula bar, copy and paste into a new sheet. Excel parses the data into individual rows. Easier than using a macro IMO


2013-11-29 11:42:48

Michael (Micky) Avidan

@Glenn,

Since my first day with "Excel" I have used range names with an underscore - like: DIA_1.
That way you'll never face a problem.

I don't recall any problem, in VBA, when using VARIABLES, such as: DIA1 or DIA2.

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2013-11-29 06:31:27

Surendera M. Bhanot

Dear hp

The problem is that when you use Alt+Enter to slpit data in a cell to start a new line (^l). when the dat is copied to word, is separated by "Line Break Command (^l) and not into "Paragraph Break (^p)". All you have to do is to use the "Find and Replace (^h)" and Replace ^l with ^p and press "Replace All" after that convert the data to table by using the "Text to Table" command. Copy the table and paste it in the worksheet. That's all. All the data is in the separate rows.


2013-11-28 11:45:23

hp

I want to split a cell into multiple columns. The several rows in a cell were entered using Alt+Enter. You said "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, ..." But I only got the first row in the cell put into a column. All the rows below the first row in the cell were deleted. Did I do anything wrong? Or Tool to text won't split multiple rows in a cell into columns?


2013-11-25 10:56:29

Glenn Case

Bryan/Micky:

Thanks for the explanation. I guess I should have known that, since I had to change some of my code when we went to Excel 2010 from 2003 because of variable names such as DIA1 and DIA2 for diameters, which suddenly didn't work any more...


2013-11-25 04:06:19

Surendera M. Bhanot

another work round is to copy the data to Word. Convert it to "Text" using 'Table to Text' tool. Replace all 'Line Breaks (^l) into 'Paragraph Breaks (^p)' using find and Replace tool. Now Convert it to 'Table' using 'Text to Table' tool. Copy the table and paste in Excel. You get the data in separate Rows.


2013-11-24 05:18:45

Michael (Micky) Avidan

Well explained, Bryan,
"Chapeau" !

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2013-11-23 10:49:59

Bryan

Glenn, the hard-coded range is the code (IV1 and A65536 represent the edges of the xl2003 worksheet. If you use the same code in an xl2007 sheet, you could have problems if you have data outside that range. Micky's code will work even if the next version of XL adds more rows/columns.


2013-11-21 10:13:56

Glenn Case

Micky:

You said "Since "Excel 2007" Birth, no one is supposed to use:

lNumCols = .Range("IV1").End(xlToLeft).Column
lNumRows = .Range("A65536").End(xlUp).Row

For obvious reasons."

What are the obvious reasons?


2013-11-16 09:40:15

Michael (Micky) Avidan

...and a "juicy" formula - no need for VBA.

http://srv1.jpg.co.il/9/5287836dedec0.png

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2013-11-16 09:20:49

Michael (Micky) Avidan

I might be wrong/tired - but if you declare the iColumn variable to be 3
The macro splits only the first cell (C1) and bypasses all other down that column.

Another approach usese a small UDF as per the following picture:

http://srv1.jpg.co.il/3/52877ee65b57d.png

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2013-11-16 08:42:36

Michael (Micky) Avidan

I might be wrong/tired - but if you declare the iColumn variable to be 3
The macro splits only the first cell (C1) and bypasses all other down that column.

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2013-11-16 08:16:14

Michael (Micky) Avidan

Since "Excel 2007" Birth, no one is supposed to use:

lNumCols = .Range("IV1").End(xlToLeft).Column
lNumRows = .Range("A65536").End(xlUp).Row

For obvious reasons. One should use:

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

Disclosure: I didn't check the rest of the code.

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL




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.