How to break up one long line of names into rows

[rickyckc]

Hi all,

Suppose I have in A1, names of people seperated by comma....

James Dean, Bruce Lee, Marilyn Monroe, Tom Cruise, etc

How do I break them into rows like

James Dean
Bruce Lee
Marilyn Monroe
Tom Cruise

Thanks.

Best Regards,
Ricky

 

[Lewiy]

First use Data>Text To Columns and pick comma as the delimiter.

Then copy the names and PasteSpecial>Transpose to convert from columns to rows.

 

[rickyckc]

Hi Lewiy,

What if I have more than 256 names, say example 400 names, will it wrap over to second row ?

Thanks.

Best Regards,
Ricky

 

[jindon]

try

Sub test()
Dim x
x = Split(Range("a1").Value, ",")
Range("b1").Resize(UBound(x)+1).Value = Application.Transpose(x)
End Sub

 

[Lewiy]

Unfortunately, it will not wrap and you will lose the excess data. However, you could always split the data manually into 2 rows quite easily beforehand.

 

[rickyckc]

Hi jindon,

Thanks, yur script works. I believe it works even if i have 5000 names ?

Best Regards,
Ricky

 

[rickyckc]

Hi jindon,

I have tested more than 1,000 names and it works. Thanks again !

Best Regards,
Ricky

 

[jindon]

Ricky

If you have more than 5000 (didn't remember exactly 5050?), tranpose function my not return correct value.
In that case

Sub test()
Dim x, i As Long, a(), i As Long
x = Split(Range("a1").Value, ",")
If UBound(x) > 5000 Then
     ReDim a(1 To UBound(x) +1, 1 To 1)
     For i = 0 To UBound(x)
          a(i + 1,1) = x(i)
     Next
     Range("b1").Resize(UBound(a,1)).Value = a
Else
     Range("b1").Resize(UBound(x)+1).Value = Application.Transpose(x)
End If
End Sub

 

[rickyckc]

Hi jindon,

Was there a typo on the first line ? I changed it to

Sub test(), a(), i As Long 
Dim x, i As Long 
x = Split(Range("a1").Value, ",") 
If UBound(x) > 5000 Then 
     ReDim a(1 To UBound(x) +1, 1 To 1) 
     For i = 0 To UBound(x) 
          a(i + 1,1) = x(i) 
     Next 
     Range("b1").Resize(UBound(a,1)).Value = a 
Else 
     Range("b1").Resize(UBound(x)+1).Value = x 
End If 
End Sub

and tested it on 4 names. What happens is it repeats the first name only, 5 times.

Thanks.

Best Regards,
Ricky

 

[rickyckc]

...sorry, i mean i changed it to this...

Sub test()
Dim x, a(), i As Long
x = Split(Range("a1").Value, ",")
If UBound(x) > 5000 Then
     ReDim a(1 To UBound(x) + 1, 1 To 1)
     For i = 0 To UBound(x)
          a(i + 1, 1) = x(i)
     Next
     Range("b1").Resize(UBound(a, 1)).Value = a
Else
     Range("b1").Resize(UBound(x) + 1).Value = x
End If
End Sub