Transpose Text

[Alex O]

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

 

[Blade Hunter]

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.