formula to Take first row of data from a cell

[tonywatsonhelp]

Hi Everyone,
I'm not sure if this is easy or difficult to sort out?
I have a list of companies, the data is all in one cell but on separate lines as shown below,
I would like a formula to Pull the First Line of data from the cell and another formula the pulls all but the first line of data from the cell?
any ideas how I can do this?

Example of data:

PBK Architects 14001 N. Dallas Pkwy, Ste 400 Dallas, Texas 75240 972-233-1323

SHW Architects, LLP 5717 Legacy Dr, Suite 250 Plano, Texas 75024 214-473-2400

Corgan Associates, Inc. 401 N Houston Street Dallas, Texas 75202 214-748-2000

PBK Architects 14001 N. Dallas Pkwy, Ste 400 Dallas, Texas 75240 972-233-1323

<COLGROUP><COL style="WIDTH: 176pt; mso-width-source: userset; mso-width-alt: 8557" width=234><TBODY>
</TBODY>

So as above I'd like to Separate the Red Data from the Black?

Please help if you can

Thanks

Tony

 

[Rick Rothstein]

Try this formula...

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

 

[tonywatsonhelp]

Thank you rick, that's the first line sorted, now how can I get everything but the first line?
Thanks
Tony

 

[Rick Rothstein]

Thank you rick, that's the first line sorted, now how can I get everything but the first line?

Give this formula a try...

=MID(A1,FIND(CHAR(10),A1&CHAR(10))+1,999)

I would note, though, that your last example which shows a blank line between the company name and the rest of the data, will have a Line Feed character at the beginning of the text returned by the formula. That blank line is really a possibility with your some of your data, then you should use this formula to make sure the output is "pure"...

=MID(SUBSTITUTE(A1,CHAR(10)&CHAR(10),CHAR(10)),FIND(CHAR(10),A1&CHAR(10))+1,999)

 

[tonywatsonhelp]

Thanks Rick, second one worked perfectly thank you very much for your help again

 

[tonywatsonhelp]

Hi rick, just one question,
because not all the data has Comers or spaces between them when we join then together I get the last word and the first word of the next line joining up like "RoadStreet" in stead of Road, street,
is there a way to add a space and a comer between them?

 

[Rick Rothstein]

Hi rick, just one question,
because not all the data has Comers or spaces between them when we join then together I get the last word and the first word of the next line joining up like "RoadStreet" in stead of Road, street,
is there a way to add a space and a comer between them?

You have to turn word wrapping on for the cell you put the formula(s) in. Select the cell(s), press CTRL+1, click the Alignment tab, put a check mark in the Checkbox labeled "Wrap text" and click OK.

 

[Mike Szczesny]

Hi Everyone,
I'm not sure if this is easy or difficult to sort out?
I have a list of companies, the data is all in one cell but on separate lines as shown below,
I would like a formula to Pull the First Line of data from the cell and another formula the pulls all but the first line of data from the cell?
any ideas how I can do this?

Example of data:

PBK Architects 14001 N. Dallas Pkwy, Ste 400 Dallas, Texas 75240 972-233-1323

SHW Architects, LLP 5717 Legacy Dr, Suite 250 Plano, Texas 75024 214-473-2400

Corgan Associates, Inc. 401 N Houston Street Dallas, Texas 75202 214-748-2000

PBK Architects 14001 N. Dallas Pkwy, Ste 400 Dallas, Texas 75240 972-233-1323

<tbody>
</tbody>

So as above I'd like to Separate the Red Data from the Black?

Please help if you can

Thanks

Tony

Hi Tony,
Assume your data is in A1
A2: =substitute(A1,char(10)," ") will show all items in this cell separated by a space (or whatever punctuation)
A3: =left(substitute(A1,char(10)," "),find(" ",A1)) shows only the first item in cell.
Hope this helps