Text to columns and transpose for variable # of columns

[eduardo_v]

Hello, thanks in advance for your help.

I have data exported from Outlook in 3 columns: To (address), To (display), and To (address type)

I want to do a text-to-columns and transpose such that each address (separated by a ";" in all three columns) gets it's own row. Easy enough to do if all rows contained the same number of addresses/names, but they range from about 1 to 10 per row.

Here's what it looks like now.

Sample:
[TABLE="class: outer_border, width: 750, align: left"]
<tbody>[TR]
[TD]To (address)[/TD]
[TD]To (display)[/TD]
[TD]To (address type)[/TD]
[/TR]
[TR]
[TD]first.last@email.com[/TD]
[TD]First Last[/TD]
[TD]SMTP[/TD]
[/TR]
[TR]
[TD]/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first1_l1[/TD]
[TD]First1 Last1[/TD]
[TD]EX[/TD]
[/TR]
[TR]
[TD]first2.last2@email.com;/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first3_l3;[/TD]
[TD]First2 Last2;First3 Last3[/TD]
[TD]SMTP;EX[/TD]
[/TR]
[TR]
[TD]/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first1_l1;first5.last5@email.com;/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first2_l2[/TD]
[TD]First4 Last4;First5 Last5;First6 Last6[/TD]
[TD][TABLE="width: 180"]
<tbody>[TR]
[TD="width: 180"]EX;SMTP;EX[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[TD][TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]EX;SMTP;EX[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]EX;SMTP;EX;EX;EX;EX[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]EX;SMTP;EX;EX;EX;EX;EX[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]EX;SMTP;SMTP[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[TD](and many other combinations and lengths)[/TD]
[/TR]
</tbody>[/TABLE]






























And I want the result to look like this, 3 columns with one address, display, and type per row:

[TABLE="class: outer_border, width: 750, align: left"]
<tbody>[TR]
[TD]To (address)[/TD]
[TD]To (display)[/TD]
[TD]To (address type)[/TD]
[/TR]
[TR]
[TD]first.last@email.com[/TD]
[TD]First Last[/TD]
[TD]SMTP[/TD]
[/TR]
[TR]
[TD]/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first1_l1[/TD]
[TD]First1 Last1[/TD]
[TD]EX[/TD]
[/TR]
[TR]
[TD]first2.last2@email.com[/TD]
[TD]First2 Last2[/TD]
[TD]SMTP[/TD]
[/TR]
[TR]
[TD]/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first3_l3[/TD]
[TD]First3 Last3[/TD]
[TD]EX[/TD]
[/TR]
[TR]
[TD]/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first4_l4[/TD]
[TD]First4 Last4[/TD]
[TD][TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]EX[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]first5.last5@email.com[/TD]
[TD]First5 Last5[/TD]
[TD]SMTP[/TD]
[/TR]
</tbody>[/TABLE]






















Thanks again for your help! Let me know if you need more details.

 

[eduardo_v]

Sorry for the terrible formatting, let me know if you'd like me to re-post (I'm not finding the 'edit post' option)