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

mouchers

New Member
Joined
Sep 27, 2006
Messages
7
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!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
Try
Code:
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

New Member
Joined
Sep 27, 2006
Messages
7
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,153
Members
410,666
Latest member
forzasec
Top