How to separate lines by carriage return in cell to multiple strings VBA

[cr731]

I have cells with multiple lines separated by carriage returns (by Alt-Enter in the cell).

I'm looking for a way to distinguish each line in VBA ... so I need to be able to cut out just the 2nd or 3rd line in that cell (and to know how many lines there were).

What would be the best way to do that? I'd prefer it to be done completely in VBA (as opposed to something like text-to-columns in the spreadsheet).

 

[Scott Huish]

Function CutLines(r As String, ParamArray linestocut()) As String
Dim t, c, d As Long, s As String
t = Split(r, Chr(10))
For Each c In linestocut
    t(c - 1) = ""
Next
For d = 0 To UBound(t)
    If t(d) <> "" Then s = s & t(d) & Chr(10)
Next d
If Right(s, 1) = Chr(10) Then s = Left(s, Len(s) - 1)
CutLines = s
End Function

Result cells will need to be set to Wrap Text.

It wasn't clear where you wanted the count.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:130px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:55px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Word1<br />Word2<br />Word3</td><td >Word1<br />Word2</td><td >Word1</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=cutlines(A1,3)</td></tr><tr><td >C1</td><td >=cutlines(A1,2,3)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

 

[Xcelfix]

Quick and easy code to split text to the following columns would be:

Sub Split2()

 Dim splitVals As Variant
 Dim totalVals As Long
 Dim CellstoSplit As Range

'Set range to split:
Set CellstoSplit = Range("R8:R9")

For Each cell In CellstoSplit
cell.Activate
splitVals = Split(ActiveCell.Value, Chr(10))
totalVals = UBound(splitVals)
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
Next cell

End Sub

 

[AlphaFrog]

Something like this returns the 2nd line from cell A1.

[color=darkblue]Dim[/color] v [color=darkblue]As[/color] [color=darkblue]Variant[/color]
v = Split(Range("A1").Value, vbLf)
MsgBox [color=darkblue]UBound[/color](v) + 1, , "Line Count"
MsgBox v(1), , "2nd line"

The split command returns a zero based array.