Extract First line in multiline cell in Excel

[Carrion]

Here is the dilemma I have an Excel table with cells that contain multiple lines. Here is an example:


-----------------------
Setup:
Theater for 160
tables in back
-----------------------


I need to be able store the first line in the cell as a variable (in this case "Setup:").

The text varies from cell to cell, so I need some code to extract the fist line only.

Thanks

 

[SkipVought]

Hi,

If you are using Excel 2k or better

FirstLine = Split(Selection.value, vbLf)(0)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[byundt]

You can do the same job as the VBA with a simple worksheet formula:
=LEFT(A1,FIND(CHAR(10),A1,1))

The VBA equivalent of this (works in Excel 97) is:

Dim cel as Range
Set cel=Range("A1")   'Source of the multi-line text
cel.offset(0,1)=Left(cel,InStr(1,A1,Chr(10)))    'Put first line in adjacent cell