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:
[TABLE="class: grid, 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]EX;SMTP;EX[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]EX;SMTP;EX[/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: grid, 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]EX[/TD]
[/TR]
[TR]
[TD]first5.last5@email.com[/TD]
[TD]First5 Last5[/TD]
[TD]SMTP[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]


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.