Outlook text list to columns

[amylex]

Hello all! Thanks in advance for your time


I have been trying to take a very large excel list such as:

Brown, Angela; Lex, Amy; Jones, Cheryl; Loria, Patty; .........

and move it to columns in excel. I've tried text to columns, and I can get it split up correctly, then I try to copy and past special : transpose but I'm just not getting the data in a usable way. I tried concatenate to get the data back to one cell but I cannot wrap my mind around how to do this. Any ideas?


THANKS!!
Amy

 

[VoG]

Is this in one cell (say A1) and you want to split it into B1:n1? Or maybe to A2:An?

 

[amylex]

yes. It is all in one cell, A1. I can manage to get it to each cell but instead of crossing 2 cells it wants to list it all in column A:


1 Lex Amy
2 Whal Cindy



it ends up:


1 Lex
2 Amy
3 Whal
4 Cindy


I'm not sure how to fix this

 

[VoG]

Assuming that the text is in A1 and you want the values in A2 down

Sub SplitEm()
Dim X
With Range("A1")
    X = Split(.Value, ";")
    .Offset(1).Resize(UBound(X) + 1).Value = Application.Transpose(X)
End With
End Sub