splitting cell content from 1st column into 4 columns
in [Functions]
|
Prev: Find duplicate emails within a single Excel column
Next: Reverse $String1, String2 to $String2 $String1?
From: Daphtg on 28 Apr 2010 16:36 In my first column I have 4 cells (1-4) (5-8) etc. with general content. The content from each of these 4 cells needs to be placed in a separate column to make a list that I can sort etc. Example: COLUMN A 1 01-04-425-001 2 Heatherridge Road #301 3 Harry Smith 4 H25 Condo Fairfax Place 5 01-05-356-041 6 McGrath Street # 56 7 Mary Jones 8 B45 Condo Lemon Circle . . . . . . and so on and so on every 4 cells. I have hundreds of 4 cell descriptions. I just can't do this one by one. Can anyone help, PLEASE, PLEASE. I am really a novice at Excel though I use it for everything everyday. There must be some kind of a formula or something. Thank you so much in advance. I can't spend the next 6 months doing this one by one or retyping each one. I have many other things to do in my job. By the way I am on a MAC 10.4.11 using Excel 2004 if that makes a difference.
From: Gord Dibben on 28 Apr 2010 17:30
In B1 enter this formula =INDEX($A$1:$A$4000,4*ROWS($1:1)-4+COLUMNS($A:A)) Drag/copy across to E1 Select B1:E1 and drag/copy down until you get zeros. Alter the 4000 if you need more rows. Gord Dibben MS Excel MVP On Wed, 28 Apr 2010 13:36:05 -0700, Daphtg <Daphtg(a)discussions.microsoft.com> wrote: >In my first column I have 4 cells (1-4) (5-8) etc. with general content. >The content from each of these 4 cells needs to be placed in a separate >column to make a list that I can sort etc. >Example: COLUMN A > 1 01-04-425-001 > 2 Heatherridge Road #301 > 3 Harry Smith > 4 H25 Condo Fairfax Place > 5 01-05-356-041 > 6 McGrath Street # 56 > 7 Mary Jones > 8 B45 Condo Lemon Circle . . . . . . and so on and so on >every 4 cells. >I have hundreds of 4 cell descriptions. I just can't do this one by one. >Can anyone help, PLEASE, PLEASE. I am really a novice at Excel though I >use it for everything everyday. >There must be some kind of a formula or something. > >Thank you so much in advance. I can't spend the next 6 months doing this one >by one or retyping each one. I have many other things to do in my job. > >By the way I am on a MAC 10.4.11 using Excel 2004 if that makes a difference. > |