| Home |
| Search |
| Today's Posts |
|
|
|
#1
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
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
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 |
|
#4
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use number in some cell as a part of other cells reference? | Excel Worksheet Functions | |||
| Using a cell reference as part of a link | Excel Discussion (Misc queries) | |||
| worksheet tab name as part of a cell reference | Excel Worksheet Functions | |||
| problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
| Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions | |||
Hybrid Mode
