Select text before carriage return
 

I am looking to develop a formula that selects the text before/after a
carriage return in a cell. For example if cell A1 has the text below in

Show name
Venue
Time

I want to find out how I can get a formula which shows me the second line
only (i.e. Venue) or the last line or the first depending on where I am using
it. ANy ideas? Im guessing it will be a left / right formula but cant quite
figure it out

Answer: Select text before carriage return
 

1. Assuming the text is in cell A1, use the FIND function to locate the position of the first carriage return. The formula would be:
   Formula:

   ---

   =FIND(CHAR(10),A1) 


   ---

2. The CHAR(10) function returns the character code for a line break, which is what a carriage return is. The FIND function then returns the position of that character within the text in cell A1.
3. Now that you know the position of the carriage return, you can use the LEFT function to extract the text before it. The formula would be:
   Formula:

   ---

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


   ---

4. The LEFT function takes two arguments: the text you want to extract from (cell A1), and the number of characters you want to extract (which is the position of the carriage return minus 1, since you don't want to include the carriage return itself).
5. This formula will return the first line of text in cell A1. To get the second line, you can use a similar formula, but this time you want to extract the text after the first carriage return. Here's the formula:
   Formula:

   ---

   =MID(A1,FIND(CHAR(10),A1)+1,FIND(CHAR(10),A1,FIND(CHAR(10),A1)+1)-FIND(CHAR(10),A1)-1) 


   ---

6. The MID function takes three arguments: the text you want to extract from (cell A1), the starting position of the text you want to extract (which is the position of the first carriage return plus 1), and the number of characters you want to extract (which is the position of the second carriage return minus the position of the first carriage return minus 1).
7. This formula will return the second line of text in cell A1. You can modify it to extract the third or fourth line by changing the starting position and the ending position accordingly.

Select text before carriage return
 

To extract the first word
=LEFT(A1,FIND(CHAR(10),A1)-1)

second word
=MID(A1,FIND(CHAR(10),A1)+1,FIND(CHAR(10),A1,FIND( CHAR(10),A1)+1)-FIND(CHAR(10),A1)-1)

third word
=TRIM(RIGHT(SUBSTITUTE(A1,CHAR(10),REPT(CHAR(32),L EN(A1))),LEN(A1)))

It is better to try out this UDF (User Defined function). From workbook
launch VBE using Alt+F11. From menu Insert a Module and paste the below
function.Close and get back to workbook and try the below formula.

Function GetString(strData As String, intWord As Integer)
GetString = Split(strData, Chr(10))(intWord - 1)
End Function


Use this formula with your data in cell A1. The second parameter is the word
number

=GetString(A1,1)
=GetString(A1,2)
=GetString(A1,3)



If this post helps click Yes
---------------
Jacob Skaria


"jellyroller" wrote:

I am looking to develop a formula that selects the text before/after a
carriage return in a cell. For example if cell A1 has the text below in

Show name
Venue
Time

I want to find out how I can get a formula which shows me the second line
only (i.e. Venue) or the last line or the first depending on where I am using
it. ANy ideas? Im guessing it will be a left / right formula but cant quite
figure it out

Select text before carriage return
 

Say your entry is in A1.

This formula in B1 will return the position of the first alt-enter:
=SEARCH(CHAR(10),A1)

This formula in C1 will return the postion of the next alt-enter after the
first:
=SEARCH(CHAR(10),A1,B1+1)

This formula in D1 will return the middle piece (Venue):
=MID(A1,B1+1,C1-B1-1)

And this formula in E1 will return the last piece (Time):
=MID(A1,C1+1,255)
(the 255 is just a big old number that's long enough for the last portion.

===========
Another way to put these values into separate cells is to:
Insert 3 columns (as many as you need) to the right of the column with the data.

Select the column with the data
Data|Text to columns (xl2003)
delimited by other
(use ctrl-j)
(hit and hold the control key while typing j)

And plop the parsed data into those new columns.

It may be easier than using the formulas.

jellyroller wrote:

I am looking to develop a formula that selects the text before/after a
carriage return in a cell. For example if cell A1 has the text below in

Show name
Venue
Time

I want to find out how I can get a formula which shows me the second line
only (i.e. Venue) or the last line or the first depending on where I am using
it. ANy ideas? Im guessing it will be a left / right formula but cant quite
figure it out

--

Dave Peterson