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:

To (address)	To (display)	To (address type)
first.last@email.com	First Last	SMTP
/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first1_l1	First1 Last1	EX
first2.last2@email.com;/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first3_l3;	First2 Last2;First3 Last3	SMTP;EX
/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	First4 Last4;First5 Last5;First6 Last6	EX;SMTP;EX <tbody> </tbody>
etc	etc	EX;SMTP;EX <colgroup><col></colgroup><tbody> </tbody>
etc	etc	EX;SMTP;EX;EX;EX;EX
etc	etc	EX;SMTP;EX;EX;EX;EX;EX
etc	etc	EX;SMTP;SMTP
etc	etc	(and many other combinations and lengths)

<tbody>
</tbody>

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

To (address)	To (display)	To (address type)
first.last@email.com	First Last	SMTP
/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first1_l1	First1 Last1	EX
first2.last2@email.com	First2 Last2	SMTP
/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first3_l3	First3 Last3	EX
/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first4_l4	First4 Last4	EX <colgroup><col></colgroup><tbody> </tbody>
first5.last5@email.com	First5 Last5	SMTP

<tbody>
</tbody>

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)