Splitting Information into Rows

@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/

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!

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.

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.

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!

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

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

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.