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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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"
 

Forum statistics

Threads
1,144,162
Messages
5,722,842
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top