How can I perform a "Text to columns and Transpose" for a 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 addresses 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
etc	etc	EX;SMTP;EX
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
first5.last5@email.com	First5 Last5	SMTP
etc	etc	etc

<tbody>
</tbody>

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

 

[Peter_SSs]

Try this in a copy of your workbook. It assumes that data is in columns A:C & writes results to columns E:G.

Sub RearrangeOutlookData()
  Dim a As Variant, b As Variant, bits As Variant
  Dim i As Long, j As Long, k As Long, z As Long, lr As Long, ubb As Long, rws As Long
  
  lr = Range("A" & Rows.Count).End(xlUp).Row
  ubb = Evaluate(Replace("sumproduct(1+len(#)-len(substitute(#,"";"","""")))", "#", "C1:C" & lr))
  ReDim b(1 To ubb, 1 To 3)
  a = Range("A1:C" & lr).Value
  For i = 1 To lr
    rws = UBound(Split(a(i, 3), ";")) + 1
    For j = 1 To 3
      bits = Split(a(i, j), ";")
      For k = 1 To rws
        b(z + k, j) = bits(k - 1)
      Next k
    Next j
    z = z + rws
  Next i
  With Range("E1:G" & z)
    .Value = b
    .Columns.AutoFit
  End With
End Sub

 

[eduardo_v]

Thank you, Peter! Perfect.

 

[Peter_SSs]

Thank you, Peter! Perfect.

No problem. Glad to help.