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.
by Allen Wyatt
(last updated August 3, 2016)
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.
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!
Need to enter a check mark into a cell? There are a number of ways you can get the desired character, depending on the font ...Discover More
Do you need to concatenate the contents of a range of cells in the same column? Here's a formula and a handy macro to make ...Discover More
Do you need to paste formulas without updating the references in whatever you are pasting? You can accomplish this, depending ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.