Formula to Transpose Data in a cell into corresponding cells

[tonywatsonhelp]

Hi Everyone,
This is similar to a post a did earlier but I want a very different result this time,
I have some company details and each company has all its information in one cell but on several rows as show below, how can I get the data from each row into its own cell?

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

Claycomb Associates Inc. 12700 Preston Rd, Ste 275 Dallas, Texas 75230 972-233-6100

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

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>

please help if you can

thanks

Tony

 

[ShawnPCooke]

Let's assume that your address information is in cell A1.

This will get you the first line; put it in B1

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

The key here is that the newline character is ASCII 10.

Now, put this in C1:

=RIGHT(A1,LEN(A1)-FIND(CHAR(10),A1))

That will give you everything BUT the first line.

Now copy the cells in B1 and C1 to D1 and E1. You want to use the relative reference, so both formulas now refer to C1. It will extract out the next line into column D. Repeat this until you have all of the lines extracted; copy and paste special as values, then delete every alternate column. You've successfully split it into multiple cells.

 

[tonywatsonhelp]

Wow, that's cleaver, thank you, just tried to works a treat, thanks
Tony