Help Separating Address Data all in 1 Column

[lovespicyfood]

Hello,

A friend of mine asked for help regarding making address labels from an Excel data file. The file has all the addresses in one column as below. There is a space of "one row" between the addresses.

I tried to do a "text to columns" but can't get it to work since there is multiple data in some cells (i.e. Daphne, AL, USA, 36526).

Is there a macro I can use to separate this data?

<TABLE style="WIDTH: 383pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=510><COLGROUP><COL style="WIDTH: 383pt; mso-width-source: userset; mso-width-alt: 18651" width=510><TBODY><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>Eastern Shore Harley-Davidson</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>7143 U.S. Highway 90</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>Daphne, AL, USA 36526</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>(251) 626-8050</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510></TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>Harley-Davidson of Dothan, Inc.</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>2418 Ross Clark Circle SW</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>Dothan, AL, USA 36301</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>(334) 792-0063</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510></TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>Rocket Harley-Davidson</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>15100 Highway 20 West</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>Madison, AL, USA 35756</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 383pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=24 width=510>(256) 340-7333</TD></TR></TBODY></TABLE>

Thanks in advance for any help you can provide!

Best,
Pete

 

[Michael M]

Hi Pete
Are you saying the ALL the data is in one cell with an ALT + Enter between each line
OR
each part of the name and address is in seperate cells, seperated by a blank row.

 

[lovespicyfood]

I'm sorry, I'm a bit of a newb to this...

Everything is in one column. Each line is "one cell" and yes, the different addresses are separated by a blank row.

Does that make sense? Sorry if I'm not being clear...and thanks so much for your help!

 

[Michael M]

So, if each line is one cell, what do you actually want to do...
Remove the blank lines ??
OR
put all the data in 1 cell ?
OR
other options ?

 

[lovespicyfood]

I want to be able to separate all of the pertinent data into different columns so that I can perform a mail merge so he can print out address labels.

Thanks again for your help, it is much appreciated.

Regards,
Pete

 

[Michael M]

I'm afraid I'm not helping much, 'cause I don't know what you want.
your 1st sample, for instance, should work just fine in a mail merge, as is

Eastern Shore Harley-Davidson
7143 U.S. Highway 90
Daphne, AL, USA 36526
(251) 626-8050

I'm obviously missing something. If you'd like to PM me with your E-Mail address
I'll send you my E-Mial ad and you can send me the workbook, if that suits.

 

[lovespicyfood]

PM sent, thank you!

 

[Armando Montes]

Try text to columns, delimited, in other type Alt+0010