Select text before carriage return

[jellyroller]

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

 

[Jacob Skaria]

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

second wor
=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),LEN(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

 

[Dave Peterson]

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.