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 June 3, 2024)
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.
Note:
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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Don't like the way that Excel pastes when you press CTRL+V? Here are some ways that you can modify the normal paste mode ...
Discover MorePutting the contents of two cells together is easy. Putting together the contents of lots of cells is more involved, as ...
Discover MoreWhen you select a cell, you typically do so to see what the cell contains to to make changes to the cell. How Excel ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-06-08 13:05:07
J. Woolley
@Mike H
TEXTSPLIT(Text,...) doesn't work well when the Text parameter is a multi-cell range or array. In that case, first join the text, then split it.
The Tip says, "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.)"
Alt+Enter produces Line-Feed, which is CHAR(10). If the data is in column A, the following dynamic array formula will join it using delimiter CHAR(10), then split it into rows using the same delimiter:
=TEXTSPLIT(TEXTJOIN(CHAR(10), TRUE, $A:$A), , CHAR(10))
Excel 2019 includes TEXTJOIN, but TEXTSPLIT currently requires Excel 365.
My Excel Toolbox includes these two dynamic array functions:
=JoinAsText(Delimiter, IgnoreEmpty, Values,...)
=SplitText(Text, [Delimiter], [CaseSensitive], [Limit], [Remainder])
The first matches Excel's TEXTJOIN. The second is similar to TEXTSPLIT but only splits into columns. Therefore, this formula uses TRANSPOSE to produce the desired result:
=TRANSPOSE(SplitText(JoinAsText(CHAR(10), TRUE, $A:$A), CHAR(10)))
For JoinAsText, see comments here: https://excelribbon.tips.net/T008663
For SplitText, see comments here: https://excelribbon.tips.net/T013906
Also, see https://sites.google.com/view/MyExcelToolbox/
2024-06-07 01:09:31
Tomek
If you think outside the box, er, Excel, you can achieve your goal by copying and pasting the cells into Word, processing them there, then copying and pasting the text back to Excel. To do this:
1. Select the relevant cells in Excel.
2. In a new Word document paste the content of the clipboard as Text Only.
Word will place your text in separate lines ending with the regular paragraph mark
(not a manual line break). However, It will place a quotation mark at the beginning
of the first line from your multi-line cell, as well as at the end of the last line from
that cell. If you try to copy and paste this text back into Excel you will get multi-line
cells back as they were. So you need to remove the quotation marks.
(see Figure 1 below) (see Figure 2 below) (see Figure 3 below)
3. Press Ctrl+H and in the Find What: in the replace-dialog box type:
"(*)" (with the quotation marks).
4. In the Replace with: type \1
5. Make sure to select "Use wildcards".
6. Click on replace all.
7. Now you can copy and paste the text back to Excel. The multiple lines from the
original cells will now occupy separate rows
One caveat: If you have other quotation marks in your cells, it may wreak havoc on the replace all execution. You can prevent that by replacing any quotation marks in your cells by another unique character, before copying them to the Word document. One option is to use a closing double smart quotation mark (”); Word in **wildcard* search and replace mode does not find smart quotes when looking for straight quotes!
Figure 1.
Figure 2.
Figure 3.
2024-06-04 05:28:05
Mike H
With the advent of newer formulas could you not use
=TEXTSPLIT(F4,,CHAR(10))
to do the same thing i.e. split the content of a cell into rows using =TEXTSPLIT.
2020-07-30 09:28:53
Thanks for the try, but your code doesn't work. I'm running it on Excel 16 for MAC. Let me know if you ever get it running.
Cheers.
2020-01-20 16:33:02
Roberto Arellano
Hi there! what about if we have two different columns with the same amount of line breaks in cells?
For example,
CELL 1 CELL2
JOSH 1001
BYRON 1002
PETER 1003
I am able to get rid of the line breaks by running the macro twice (aiming different column each time) and then replacing the columns, but wondering if there could be a solution that handles both cells on one time.
thanks!
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments