Split $tring into Parts and Paste output into multiple Column/Cells

[Papa_Don]

Hi group,

I have a column within a spreadsheet that contains email addresses. Each row can have one (1) email address or it can have multiple email addresses (occasionally as many as 5 or 6 email addresses). As an example, the spreadsheet could look like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]tom.kennedy@company.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]StacyJohns@company.com; BrendaCobb@company.com; Jason.Anders@company.com; Robert.Morgan@company.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brenda.Freeman@company.com; Karen.Hazen@company.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to create a macro that will:

1) if column A has only 1 email address, move that email to column B
2) if column A has multiple email addresses, break them apart and place the first email address in column B, the second email address in column C, the third email address in column D and so on.

The macro should make it look like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]tom.kennedy@company.com[/TD]
[TD]tom.kennedy@company.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]StacyJohns@company.com; BrendaCobb@company.com; Jason.Anders@company.com; Robert.Morgan@company.com[/TD]
[TD]StacyJohns@company.com[/TD]
[TD]BrendaCobb@company.com[/TD]
[TD]Jason.Anders@company.com[/TD]
[TD]Robert.Morgan@company.com[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brenda.Freeman@company.com; Karen.Hazen@company.com[/TD]
[TD]Brenda.Freeman@company.com[/TD]
[TD]Karen.Hazen@company.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What would be the best way to do this? I've seen suggestions to use "Split", but truthfully I don't have a complete grasp of arrays and how they work. So I'm not sure how to code this. Therefore I am very open to your ideas and suggestions.

In advance, thanks for any help you can offer. I look forward to hearing from you.

Don

 

[mistahk]

I don't think you need a macro for this. If you click on the DATA tab in Excel, you will see a "Text to Columns" button. Highlight column A and click on that. Then select delimited, and input semicolon as your delimiter, then finish. You should get the results you want.