Reference to a Variable Part of a Cell

[Hilvert Scheper]

Hi there,
Can I Please ask the following question;
Is it possible to create a Cell-reference (as a Formula) that will give me
just the first line of Text in a Cell?
Example: I have this Text in just One Cell:

(Variable) CUSTOMER NAME
Address Line 1
Address Line 2
Address Line 3
Etcetera
Etcetera

Then, in another Cell I now Only need the First Line ("CUSTOMER NAME"),
however many caracters are in that First Line.
Is this at all possible with "LEN", "LEFT", "MID" or a similar formula?
Many Thanks in advance for Your help,
Hilvert Scheper

 

[Rick Rothstein]

Try this...

=IF(ISNUMBER(FIND(CHAR(10),A1)),LEFT(A1,FIND(CHAR(10),A1)-1),A1)

where I have assumed your multi-lined text is in A1.

 

[Bob Phillips]

=LEFT(A1,FIND(CHAR(10),A1)-1)

 

[Rick Rothstein]

Actually, use this as it properly handles an empty cell...

=IF(A1="","",IF(ISNUMBER(FIND(CHAR(10),A1)),LEFT(A1,FIND(CHAR(10),A1)-1),A1))

 

[Hilvert Scheper]

Fantastic, Just what I needed.
Thank You Very Much indeed Rick!!

 

[Hilvert Scheper]

Thanks a Million Bob,
Nice and Short, again Just what I needed.
Hilvert

 

[Rick Rothstein]

Just so you are aware, the last formula I posted will handle one text line,
multiple text lines and blank cells. If you know you will **always** have
multi-line text in a cell when the cell is not blank, then you can simplify
the formula to this...

=IF(A1="","",LEFT(A1,FIND(CHAR(10),A1)-1))

 

[Hilvert Scheper]

Hi Rick,
In my specific case the Cell will Definitely ALWAYS have Text in there with
Multiple Lines,
So in this case I will just use the Basic formula:
=LEFT(A1,FIND(CHAR(10),A1)-1)

Again, Many Thanks for Your efforts, Very Much appreciated.
Hilvert