Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 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.
Written by Allen Wyatt (last updated April 6, 2019)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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.
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 Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Splitting Information into Rows.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
There are many different ways you may need to enter data in a worksheet. For instance, you might want to enter data in ...Discover More
Need a quick memory jog when entering a worksheet function? Here's a shortcut that will be invaluable.Discover More
Under the right circumstances, you may notice problems when copying dates from one workbook to another. This tip explains ...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.