|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
September 3rd, 2008, 09:55 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 
|
|
#2
September 3rd, 2008, 10:11 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#3
September 3rd, 2008, 10:13 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#4
September 3rd, 2008, 10:14 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#5
September 3rd, 2008, 10:19 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#6
September 3rd, 2008, 10:30 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#7
September 3rd, 2008, 10:36 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#8
September 3rd, 2008, 11:40 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
