Giving each item on a list all in 1 cell a cell of its own

[mouchers]

Hi, I have a list contained within 1 single cell (Cell A1), i.e.;

90046
62534
98163
98362
34263
etc.

I want to change it so that the list isn't all in one single cell and even though it'll still be a list, each item on the list will have it's own individual cell i.e.;

90046 (will be in A1)
62534 (will be in B1)
98163 (will be in C1)
98362 (will be in D1)
34263 (will be in E1)
etc.

Is my only option to insert dozens of rows and cut and paste each item on my list into it's own cell?

Thanks in advance!

 

[jindon]

Hi
Try

Sub test()
Dim a, b(), i As Long, n As Long
a = Range("a1",Range("a" & Rows.Count).End(xlUp)).Value
For i = 1 To UBound(a,1)
   ReDim Preserve b(n)
   b(n) = Split(Replace(a(i,1),Chr(32),""),Chr(10))
   n = n + 1
Next
With Range("a1")
   For i = 0 To UBound(b)
      .Offset(i).Resize(,UBound(b(i))+1).Value = b(i)
   Next
End With
End Sub

 

[mouchers]

Wow, thanks for the help jindon but I don't think I'd know where to start with that one, where and how do I input the information that you have given to me?

Thank you very much.

 

[jindon]

1) Hit Alt + F11 to open VB Editor
2) go to [Insert]-[Module] then paste the code
3) Hit Alt + F11 again to get back to Excel
4) go to [Tools]-[Macro]-[Macro], choose "test" then hit "Run"