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.
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.
Learn more about Allen...
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.
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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!