CODEWITHSUNDEEP
vbaexcel
J. Taylor
J. Taylor

Reputation: 11

VBA - How to get around carriage returns

I think this should be fairly easy to do but I am struggling:- I have a cell with data which is separated by multiple carriage returns. I always want the second line of data but I am unsure how to pull just this out? Data example is like below:

Line 1: This line can be any length
Line 2: Same with this one
Line 3: so on
Line 4: and so forth
Line 5: etc

Thanks,

James

Upvotes: 0

Views: 248

Answers (1)

Gary's Student
Gary's Student

Reputation: 96781

With data in A1, in another cell enter:

=TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",999)),2*999-998,999))

enter image description here

If this does not work, use CHAR(13).

To do this in VBA:

Public Function SecondLine(s As String) As String
    s = Replace(s, Chr(13), Chr(10))
    s = Replace(s, Chr(10) & Chr(10), Chr(10))
    SecondLine = Split(s, Chr(10))(1)
End Function

Upvotes: 1

Related Questions