How can I perform a "Text to columns and Transpose" for a variable # of columns?

eduardo_v

New Member
Joined
Jun 8, 2016
Messages
6
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.comFirst LastSMTP
/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first1_l1First1 Last1EX
first2.last2@email.com;/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first3_l3;First2 Last2;First3 Last3SMTP;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_l2First4 Last4;First5 Last5;First6 Last6EX;SMTP;EX
etcetcEX;SMTP;EX
etcetcEX;SMTP;EX;EX;EX;EX
etcetcEX;SMTP;EX;EX;EX;EX;EX
etcetcEX;SMTP;SMTP
etcetc(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.comFirst LastSMTP
/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first1_l1First1 Last1EX
first2.last2@email.comFirst2 Last2SMTP
/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first3_l3First3 Last3EX
/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHFgds23SPDLT)/cn=Recipients/cn=00ce82a2bcff4fe7a428733601c2994f-first4_l4First4 Last4EX
first5.last5@email.comFirst5 Last5SMTP
etcetcetc

<tbody>
</tbody>


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

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,193
Office Version
365
Platform
Windows
Try this in a copy of your workbook. It assumes that data is in columns A:C & writes results to columns E:G.

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,360
Messages
5,486,387
Members
407,544
Latest member
mguevara

This Week's Hot Topics

Top