Splitting multiple cell contents from 1st column into 4 columns

n 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. 
0
Utf
4/28/2010 9:34:08 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
655 Views

Similar Articles

[PageSpeed] 56

See reply at your earlier post.


Gord Dibben  MS Excel MVP

On Wed, 28 Apr 2010 14:34:08 -0700, Daphtg
<Daphtg@discussions.microsoft.com> wrote:

>n 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. 

0
Gord
4/28/2010 9:43:42 PM
I am sorry but I don't have an earlier post.  I posted yesterday in both 
General Questions and Worksheet Functions and have received no replies except 
this one.

Thank you.


"Gord Dibben" wrote:

> See reply at your earlier post.
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Wed, 28 Apr 2010 14:34:08 -0700, Daphtg
> <Daphtg@discussions.microsoft.com> wrote:
> 
> >n 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. 
> 
> .
> 
0
Utf
4/29/2010 4:34:08 PM
You do have another post in excel.worksheet.functions and here is my reply
to that post.

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 Thu, 29 Apr 2010 09:34:08 -0700, Daphtg
<Daphtg@discussions.microsoft.com> wrote:

>I am sorry but I don't have an earlier post.  I posted yesterday in both 
>General Questions and Worksheet Functions and have received no replies except 
>this one.
>
>Thank you.
>
>
>"Gord Dibben" wrote:
>
>> See reply at your earlier post.
>> 
>> 
>> Gord Dibben  MS Excel MVP
>> 
>> On Wed, 28 Apr 2010 14:34:08 -0700, Daphtg
>> <Daphtg@discussions.microsoft.com> wrote:
>> 
>> >n 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. 
>> 
>> .
>> 

0
Gord
4/30/2010 1:42:50 PM
Reply:

Similar Artilces:

Auto-update Fill Series in column that has randomly spaced blank c
Hi, I have a worksheet of projects listed by quarter. A cell (say A6) contains QTR 1, followed by cells in column A filled with a series (1,2,3...say up to 12). Then comes a blank row followed by a row containing words "QTR 2" and then the fill series continues from where it left under QTR 1 i.e. 13,14,15...say up to 18. And so on for QTR 3 and QTR 4. When a new project is added, I want to be able to add a row and with minimal steps, want the fill series below this added new project, to update. When I delete a row (project), I want the fill series to automatically ...

change last serie to be line in the line-column Excel chart in Wor
Hi, I'd like to insert the Excel chart in Word doc. When I insert an Excel chart with 4 series in the type of line-column. The second serie always to be line, I want to the last serie to be line rather than second one. I know how change it when it happens in Excel. But I cannot find the all of excel Menus in Word windows when I edit chart in the word, so I donot know how change it? Thanks a lot for anyone offering help. Hi, That depends on how you copied the chart into word? -- Thanks, Shane Devenshire "Eric" wrote: > Hi, I'd like to insert the Excel chart in ...

Sorting pivot table by specific field (column)
Hey guys (and girls), Anyone know how to sort a pivot table by a specific field (not the total sum of the fields)? For instance, if I have 5 years of data 2002-6 and 20 countries. If I make a pivot table of these I get 21 rows (the countries + total) and 6 columns for years (5 years of data + total). If I use the Field Settings --> Advanced --> Sort by field the Pivot table (PT) will be sorted by the sum of the different fields. What if I don't want this, but rater want to sort it by for instance year 2003. How do I do that? Anyone have a nice and clever solution? Much appreciated...

Using cell reference with logical operator in DGET expression
I am using DGET to search an array in the worksheet to find a particular percent to use in a calculation elsewhere in the spreadsheet. The row members of the array contain a series of from and to values that I use to identify which row has the percent I am looking for. I have defined the array as a range and I can use the logical operatirs with numeric values in the range criteria and everything works fine. For example, I can use <500 in the appropriate cell in range criteria to find the percent to use when the value for that column in the array is less than 500. All working f...

reference cell above even if row deleted
I would like to be able to reference a cell imediately above the current cell even if the row above that cell had just been deleted. For instance: contents of A5: =A4+$B$1 then delete row 4 and rather than have contents of A4:#REF!+$B$1 have it A4:A3+$B$1 Is there some way to do this? Thanks for any tips. John Keith kd0gd@juno.com You can use the OFFSET function: =OFFSET(A5,-1,0)+$B$1 John Keith wrote: > I would like to be able to reference a cell imediately above the > current cell even if the row above that cell had just been deleted. > > For instance: > > conte...

Multiple Condition Formatting
Hi, Can anyone help me on this: =IF(AND(F65<>0,G59="SELECT CUSTOMER"),APPLY FORMATTING, DON'T APPLY FORMATTING) I have been exploring the Conditional Formatting in 2007 and I may be wrong but I don't see a way to set a condition whereby two(2) logical conditions must be met for it to apply the formatting. Any ideas?? http://excel.tips.net/Pages/T002980_Conditional_Formats_that_Distinguish_Blanks_and_Zeroes.html This could easily be applied to other conditions. Mike "Gerard Sanchez" wrote: > Hi, > > Can anyone help me on this: > ...

Create a list with multiple criteria
I have a data base with 1,000's of addresses w/zipcodes (Sheet 1 columns A to E, E being zipcodes). After setting a base address I get all of the zipcodes within a given radius (in this case 70 zipcodes), listed in Sheet 2, Column A. Next, I use COUNTIF (in column B) to find how many addresses are in each zipcode, in this example there are 46 addresses within the 70 zipcodes. What I want to do is create a list in sheet three that will list each with the data from Sheet 1 columns A to E). Any help would be appreciated. Ronbo ...

SumIFS Cell
I am trying to reference a cell in a formula and I would like to say anything greater then cell J2 but when I input this it searches for text. =SUMIFS($B$2:$B$27,$A$2:$A$27,"000001",$C$2:$C$27,>D2) A B C D 1 00001 100 40248 40248 2 12001 150 40237 3 15001 200 40237 4 00001 150 40290 5 00001 50 40350 I would like the total to return 200 because Cell C5 and C4 are larger than Cell D1 in respect to the Sku number I would like to sum. But when I put in >J2 it enters “>J2” and it won’t return a value other then 0. (If I put in just D2 then it returns a valu...

Combining multiple Stacked Charts
I have 3 scenarios with info on Asset A and Asset B for 10 years. I can create stack charts for each scenario independently, but can not figure out how to combine all 3 onto 1 chart across multiple years. x axis is year and each year should have 3 stacked lines Y Axis should be total value of both Assets Any help appreciated Does this help? http://peltiertech.com/WordPress/2008/05/19/clustered-stacked-column-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JANeyer" <...

multiple colors on format of plot area
How can I change the format of the plot area to multiple colors. for example I would like to have a Bar Chart with the background plot area starting @ blue , then Green, then Red. Then have the temperature bars plot over this area. thanks You can do so by bringing a stacked column chart into the mix. Please see Jon's example below: http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html -- John Mansfield http://cellmatrix.net "BillO" wrote: > How can I change the format of the plot area to multiple colors. > > for example I would like to have a ...

Table Cell
I have created a form using a table. I want to lock or block cells that should not be changed. How do you do this? ...

combine multiple excel file in to one excel file and multiple worksheet
I am wanting to use the following code to combine worksheets from multiple files. However I would like to be able to select folder which contains files in a more automated way that having to change the code every time, and also copy all worksheets with links and formulas removed. Any help on this is greatly appreciated as I have limited code knowledge. Sub Copy_them() > Dim TargetWkbk As Workbook > Dim mrgWkbk As Workbook > Dim i As Long > Dim Wks As Worksheet > Dim fName As String > Application.ScreenUpdating = False > Set TargetWkbk ...

multiple text edit
Hi all, I'm a beginner when it comes to Visio. I’m using Visio 2007 and I’m wondering if the following is possible. What I want to be able to do is to change the text in a number of textboxes by just editing one of them - that is, when I change the text in one textbox, the three other textboxes will display what I’ve just written automatically. Can this be done? Thanks in advance. the answer is "kinda". You can coordinate text between shapes so that changing a specific shape will be mirrored to others. It's a one to many, you don't get the option of changing anyo...

Round to a Multiple
In an Access Report how do i round to the nearest multiple? ex. 45263 Round to 45260 45266 Round to 45270 Thanks, On Fri, 18 Jan 2008 16:40:14 -0600, "mh" <hes_mhjr@bellsouth.net> wrote: >In an Access Report how do i round to the nearest multiple? > >ex. 45263 Round to 45260 > 45266 Round to 45270 > >Thanks, Multiple of 10? 10*Round([field]/10,0) John W. Vinson [MVP] There's a bunch of rounding info here: http://allenbrowne.com/round.html#RoundNegativePlaces -- Allen Browne - Microsoft MVP. Perth, Western A...

Cell Protection #8
Hello: I am using the UserInterfaceOnly to protect my worksheet. I have it placed in the workbook object. Unfortunately, after the workbook is open the user can Tools>>Protection>>Unprotect Sheet. Is there a way to password out this option? I would like the user to have access to only the standard toolbar, is there a method to stop them from enabling various toolbars, menus options and manipulating the sheet, etc. Kind regards, D.Parker Wouldn't it be simpler to use a password that the user doesn't know? And don't forget to protect your project. In th...

Active Directory Problem
Hi Everyone, Hopefully you can help me out a bit here. Long story short, we had a failure last week that we were unable to recover Active Directory from. As such, we've had to rebuild AD from scratch - now the domain name and all user accounts are recreated and identical to the previous setup. However, I can no longer access CRM. I assume CRM 4.0 is authenticating on, not only AD membership, but based on an AD GUID of some sort - which I can't replicate. How can I get back in to re-load my users? Thanks, Craig Hi, Did u check if the users were part of CRM groups created ...

Multiple emails for one contact in distribution list
I have created a group to send out bi-monthly emails from my personal email account. One of the people in the group would like to have the email sent to her work email and to her personal email. I have attempted to add both email addresses to the group, and initially it does. After syncing, however, the personal email disappears from the group list because it is not the primary email listed in the contact. Is there a way to include both email addresses without creating a separate contact for just the personal email? I am running Outlook 2010 beta. My email is through Hotmai...

Setting up multiple accounts
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Hi guys, <br><br>Just switched over to the mac world and am now setting up my email. <br><br>I have 2 separate email address all hosted on the same mail server - mail.tpg.com.au <br><br>I added my default email address which works fine in Entourage but I continue to get the following error message with my second email account: <br><br>Mail could not be received at this time. <br><br>The server for &quot;Cheekyage's&quot; r...

How do you move the curser around in a cell
What do mean by "cursor" and what do you want to do? HTH Otto "krisf" <krisf@discussions.microsoft.com> wrote in message news:8BD5F197-4745-4FD3-95BE-80A10897020F@microsoft.com... > ...

User forms #4
Rather than linking excel to a VB front end, how powerful are the VB userforms? If I want to create a front end of my excel workbook with sheets which will just display information that the cells calculate th how hard would this be? I also want to try and hide the fact that it is an excel spreadsheet? Thanks in advance Padrai -- Message posted from http://www.ExcelForum.com Shouldn't be too hard, especially if you know VB! To hide the fact the file is in excel you can do things such as hidin worksheets and also hiding toolbars when the worbook_open event i triggered. This can al...

Data label above columns??
Hi, I have some stacked column charts and I want the data labels to appear just above each column. Is that possible? I'm using excel 2003 version and the only options i can see for the label positions are "inside end", "center" and "inside base" - so at the moment I'm moving each data label manually and this is very time concuming! Thanks in advance for any advice you offer :) K I'm afraid it's not an option due to the confusion it would cause. If the label is above the stack it refers to, it could be sitting on top of another stack, th...

Copying decimal data to another column to integer 'on the fly'
Hi, I am working on Asset registers/Depreciation Schedules and at the star of the year I need to convert over my previous years spreadsheets (fo about 40 schools) to the new financial year. This means moving the accumulate depreciation at the end of the previous year to being the 'openin depreciation' for the new year. I therefore want to copy a column of figures to another column an paste to that column as values only BUT as INTEGERS. I am running Excel 2003 - 'Paste Only' can solve the 'values' part bu does not have conversion to an integer 'on the fly'. ...

mass definitions of cell names
Ok, I have a problem. I have to name a large number of cells in different sheets. I'm hoping that there is a way in a macro, or some other way to define these in an easier way other than one by one. Here is an example: 1 2 3 4 1001 --------- --------- --------- --------- 1002 --------- --------- --------- --------- 1209 --------- --------- --------- --------- 1210 --------- --------- --------- --------- This would be the column and row headers for the cells. Below is how the...

Moving a line of data when information is entered in a specific cell.
HELP NEEDED. I am trying to create a spreadsheet to keep track of problems that we encounter at our volunteer organization. It is pretty simple. I am having one problem. One of my fields is "completion information." What I need is when completion information is entered into the field, I want that line of information to move to an "archived sheet". In effect, so that I have a sheet with only active problems and when the completion information is entered it moves to the archived sheet. Anyone have any suggestions? -- opshmo -----------------------------------------------...

White gridlines that visible over columns?
Does anyone know if or how I can have white major gridlines that are in effect 'on top of' the columns in a chart? I'm trying to create a white backgrounded histogram, with white horizontal gridlines that are effectively only visible as they 'bisect' the coloured columns. Any help appreciated. Jay Hi, You can create your own gridlines using an additional data series plotted as an XY scatter chart. then use the X Error bars to draw your lines. Cheers Andy Jay wrote: > Does anyone know if or how I can have white major gridlines that are in > effect 'on to...