Transpose Text

Alex O

Active Member
Joined
Mar 16, 2009
Messages
345
Office Version
  1. 365
Platform
  1. Windows
Is there a way to transpose text (my sample is below) from rows to columns?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 539px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>M</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">PS Executive Centers,Inc</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">1991 Crocker Rd, Suite 600</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">Gemini Towers</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">Cleveland,Ohio 44145</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">Maggie Haas </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">PAUL KINDRED & STAFF </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">GARTNER GROUP </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">FORT MEYERS, FL 33913</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">239-561-4000</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">Mike Lee & STAFF </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">MERCHANTS FOODSERVICE</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">1100 EDWARDS ST</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">HATTIESBURG MS 39401</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">601-583-4351</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">MIKE SAYRE </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">SCREEN USA</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">5110 TOLLVIEW DRIVE </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">ROLLING MEADOW IL 60008</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">847-870-1960 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">MIKE MCGILL</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">SANTA BARBARA COMMUNITY PHONE BOOK</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 9pt">5266 HOLLISTER AVE # 205</TD></TR></TBODY></TABLE>

Need it to be:


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 266px"><COL style="WIDTH: 278px"><COL style="WIDTH: 205px"><COL style="WIDTH: 89px"><COL style="WIDTH: 123px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 99px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Name</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Company</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Address (**No P.O. Boxes)</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Suite/Apt.</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">City</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">State</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Zip</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Phone</TD></TR></TBODY></TABLE>
Thanks
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Copy the data then tick transpose in the paste special dialogue box (Hold shift and click edit to get paste special in pre 2007 or click the drop down under paste on the home ribbon bar in 2007+).

It's not going to be perfect though because your data is varying row lengths, you may need to write some VBA code to work out the logic of what the data is and place in the correct cell.
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,730
Members
452,939
Latest member
WCrawford

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top