Easier way to transpose address data

[jbmillvb]

I have a lot of addresses given to me in Excel in this format:

<TABLE style="WIDTH: 131pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=174><COLGROUP><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6363" width=174><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 131pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=174>Mr. & Mrs James W. Carson</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>443 Longview Street</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Decatur, AL 35603</TD></TR></TBODY></TABLE>

Each part is in its own box and I would like to get it into colums by name, address, city state zip...<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=543><COLGROUP><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436" width=176><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339" width=146><COL style="WIDTH: 166pt; mso-width-source: userset; mso-width-alt: 8082" width=221><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 132pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=176>Mr. & Mrs. James W. Carson</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 110pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=146>443 Longview Street</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 166pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=221>Decatur, AL 35603</TD></TR></TBODY></TABLE>
but faster than copy, paste special and transpose. I have about 200 addresses in this format that I need to get into the colum format so I can make lables. Any easier/faster way to do this?

 

[Jonmo1]

Is it always 3 rows for each person?
1 row for name
2nd row for address
3rd row for city state zip

Then repeated in the next 3 rows

?

 

[jbmillvb]

Yes, all of them are like that.

 

[Rick Rothstein]

Yes, all of them are like that.

Just double checking... there are no blank cellse between each set of 3 data rows?

 

[nicekev500]

Sub ColtoRows()
Dim Rng As Range
Dim I As Long
Dim j As Long
Dim nocols As Long
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For I = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(I, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
End Sub

***** just say how many columns you need as sometimes you might need 3 or 4 or 5 depending on the detail *****