How to separate multi-line data in cell?

[NikkoW]

Hi, experts. I hope you can help.

I have been supplied with a spreadsheet where several cells contain multiple
lines of data separated by hard line breaks (Alt + Enter, I think but not
sure).

I would like to take each line of data in that cell and put them into
separate cells below the original. For example:

Col_1_Text Col_2_Text Col_3_Multi-line_Text_Cel

would become

Col_1_Text Col_2_Text Col_3_Text_From_Line_1
Col_1_Text Col_2_Text Col_3_Text_From_Line_2
Col_1_Text Col_2_Text Col_3_Text_From_Line_3

I know that the "Text to columns" feature will divide the lines and put them
in separate columns but I want the data in separate rows. I could then
manually move them but that will be tedious with the amount of data I have.

Any suggestions?

Thanks

Nick

 

[PeterAtherton]

-----Original Message-----
Hi, experts. I hope you can help.

I have been supplied with a spreadsheet where several cells contain multiple
lines of data separated by hard line breaks (Alt + Enter, I think but not
sure).

I would like to take each line of data in that cell and put them into
separate cells below the original. For example:

Col_1_Text Col_2_Text Col_3_Multi- line_Text_Cel

would become

Col_1_Text Col_2_Text Col_3_Text_From_Line_1
Col_1_Text Col_2_Text Col_3_Text_From_Line_2
Col_1_Text Col_2_Text Col_3_Text_From_Line_3

Any suggestions?

Thanks

Nick

To remove hard Returns use Edit, Replace (Ctl + H)on the
Find textbox type ALT + 010 on the numberpad and press
replace all.

To swap the data from columns to rows use transpose on the
PAsteSpecial Menu. i.e. copy the data, select a location
to copy the data, choose Edit, PasteSpecial and cllickthe
Trnspose checkbox.

Regards
Peter

 

[Dave Peterson]

how about a little macro:

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim myCell As Range
Dim oRow As Long
Dim PartsCtr As Long
Dim myStr As String
Dim mySplit As Variant

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

oRow = 1
With curWks
For Each myCell In .Range("c1", _
.Cells(.Rows.Count, "C").End(xlUp)).Cells
myStr = myCell.Value
If myStr = vbLf Then
myStr = Left(myCell.Value, Len(myCell.Value) - 1)
End If
mySplit = Split97(myStr, vbLf)
PartsCtr = UBound(mySplit) - LBound(mySplit) + 1
newWks.Cells(oRow, "A").Resize(PartsCtr, 2).Value _
= myCell.Offset(0, -2).Resize(1, 2).Value
newWks.Cells(oRow, "C").Resize(PartsCtr).Value _
= Application.Transpose(mySplit)
oRow = oRow + PartsCtr
Next myCell
End With

End Sub
'from Tom Ogilvy
Function Split97(sStr As Variant, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

If you're using xl2k or higher, you can remove the Split97 function (at the
bottom). But remember to change split97 to split in this line:

mySplit = Split97(myStr, vbLf)
becomes
mySplit = Split(myStr, vbLf)

(Split was added in xl2k.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm