Reference to a Variable Part of a Cell

  • Thread starter Thread starter Hilvert Scheper
  • Start date Start date
H

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
 
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.
 
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))
 
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))
 
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
 
Back
Top