Reference to a Variable Part of a Cell
 

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

Reference to a Variable Part of a Cell
 

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.

--
Rick (MVP - Excel)


"Hilvert Scheper" wrote in
message ...
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

Reference to a Variable Part of a Cell
 

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

--
__________________________________
HTH

Bob

"Hilvert Scheper" wrote in
message ...
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

Reference to a Variable Part of a Cell
 

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

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Hilvert Scheper" wrote in
message ...
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

Reference to a Variable Part of a Cell
 

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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Hilvert Scheper" wrote in
message ...
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

Reference to a Variable Part of a Cell
 

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

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Hilvert Scheper" wrote in
message ...
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

Reference to a Variable Part of a Cell
 

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))

--
Rick (MVP - Excel)


"Hilvert Scheper" wrote in
message ...
Fantastic, Just what I needed.
Thank You Very Much indeed Rick!!

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Hilvert Scheper" wrote in
message ...
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

Reference to a Variable Part of a Cell
 

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

"Rick Rothstein" wrote:

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))

--
Rick (MVP - Excel)


"Hilvert Scheper" wrote in
message ...
Fantastic, Just what I needed.
Thank You Very Much indeed Rick!!

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Hilvert Scheper" wrote in
message ...
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