# Formula to Transpose Data in a cell into corresponding cells

#### tonywatsonhelp

##### Well-known Member
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>

thanks

Tony

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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.

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

Replies
7
Views
361

1,214,915
Messages
6,122,214
Members
449,074
Latest member
cancansova

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back