Trying to parse data that has line breaks

[Bfish]

I am trying to parse some data into seperate colums either by using a macro or some VBScript but I'm not sure how to go about doing it. Currently my data looks like this.
Column A
John Smith Company
Contact Person: John Smith
Ste 555
5500 Main St
Kansas City, MO 64112
Phone: (555) 555-5555
Fax: (555) 555-5555
Email: john.smith@johnsmith.com
Website: www.johnsmith.com

I want it to look like this.
<TABLE style="WIDTH: 911pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1213 x:str><COLGROUP><COL style="WIDTH: 141pt; mso-width-source: userset; mso-width-alt: 6875" width=188><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 176pt; mso-width-source: userset; mso-width-alt: 8594" width=235><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 141pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=188>Column A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=114>Column B</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 110pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=147>Column C</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=81>Column D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 32pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=42>State</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 32pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=43>Zip</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=100>Phone</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=94>Fax</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 176pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=235>email address</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 127pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=169>Website</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>John Smith company</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">John Smith</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Ste 555, 5500 Main St</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Kansas City</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">MO</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>64112</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">(555) 555-5555</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">(555) 555-5555</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24>john.smith@john.smith.com</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24>www.johnsmith.com</TD></TR></TBODY></TABLE>

When it is all bunched in the one column each line is seperated by a line break (displayed in excel as a little square box). Can I seperate the data by using a macro or do I have to use VBScript and if it is with VB where do I put it?

Any help would be greatly appreciated. Thanks!!

 

[Bfish]

Ok Ruddles here we go. I ran the latest script you sent. It seperated the phone, fax, email, and website perfectly. They all have their own column now. Here is what i'm seeing on everything else. As far as the address go city, state and zip are all still in the same column. I would like to have a column for each of them. I'm also seeing that there are some countries on here outside of the US, and those have the country listed. I hadn't actually planned on having a country column but I suppose I may have to now. Also the little square marking the line break is still there at the end of the data in each column.

 

[Ruddles]

I don't know how to identify a country. I need some sample data. I'll PM you my email address.

The little square... I don't see it. Can you apply this formula to it and tell me what it is?

=CODE(RIGHT(A1,1))

(Replace A1 with tha address of a cell which shows the little square.)

 

[Bfish]

Ruddles thanks for all your help. I think we got it to where it needs to be now. I appreciate it greatly. Thanks!!