Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Splitting Up An Excel File By Rows

I am trying to split up an excel file that has 18000 lines, i want to break this into groups of 100 lines and retain the original header info on line one for each file. Is this possible to do with a macro. I have NO experience with macros of programming so go easy on me. It would be great if it could autosave as book 1 book 2 book 3 or whatever, file name doesn't matter that much.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Splitting A Large File Into Two And Mining The Data
I've been scouring the Internet for a few days now trying to get a macro working with varying degrees of success. It's driving me mad and I'm really hoping that someone out there can help me out. I'm trying to write a macro that will ...

1. Take a .xls file

2. Split the file in two

3. Take cells B2:B11 and paste them in to a csv file (to be called Upload1.csv) using the rows as column headers (i.e. B2:B11 now become A1:J1)

4. Take cells A13-J13 downwards (this file will have different numbers of rows each time it's generated) and paste them into a different csv file (to be called Upload2.csv) populating all the rows (except all rows in column I which are to be left blank) and all rows in Column J which are to contain the info in cell B7 of the original document.

5. In cell B11 of the original document I must only take the information that says MyAddress:xxxxx and discard all the other info

6. I must also loop through the second document until I find the words Total and copy everything UNTIL that row into the new document.

I know this probably sounds pretty awkward but in practice it's not that difficult to understand, it's probably the way that I've explained it

So far I've managed to take the original document and split it, populate both CSV files but I'm having problems with looping the macro until it sees the word Total and discards it and also populating the appropriate rows with the info from B7 ... I don't know how to make it populate only the rows that have info on them.

I've attached the original Excel file that is generated and also my attempt at the 2 csv files. I've also written in RED what needs to go where in each document.

View Replies!   View Related
Splitting Document To Mine Data And Create CSV File
I've got an Excel document that is generated from a third party programme (See ExcelDoc attached) ....

View Replies!   View Related
Splitting Data From A Cell To Multiple Rows
Faced with a problem. I have steps for doing testing in one cell but I need to put them in multiple rows.

Example ....

View Replies!   View Related
Sorting Rows Of Data And Splitting Into Different Sheets
I have sets of data (A1....F50000 or more) that I need to sort out based on the cell value in column F. Based on the cell value I would like the info on the row (A to F) put into a different sheet. There would be 8 different sheets and each sheet would include 1 to 50 different sorts from column F.

The macro could start with one material sort each and I could fill in the rest of the sorts. The 8 different materials (one for each sheet to start) could be "34b2p""ptop27""pdw19""p58leg""ppdpts""mirror""Pdoor""bent""p4545".

View Replies!   View Related
Text File Import With Text To Columns Splitting Same Text Differently
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.

I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.

FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))

View Replies!   View Related
Import Text File Into File Where Rows Exceed Excel Row Limit
I have a workbook with many spreadsheet named Sheet1, Sheet2 and so on. Each sheet is filled completely upto 65536 rows. This data is being picked up from a CSV file. In this file there are sites with each site there is a assciated set of data. What happens is the data that extracts data does not differntiate between sites and when it reaches to the end of worksheet it splits the data into next sheet. So I am trying to create a macro to check each spreadsheet starting with the last sheet in the work book for example last sheet in the workbook is seven it should go to sheet6 and if there is a blank row after row 64000 it should cut all the rows and move them to sheet7. Then it should goto sheet5 and do the same and keep on doing it until it reaches sheet1.

View Replies!   View Related
Splitting A List
I have a To-do list of tasks in column A.

I want to split it up into assignments for different days of the week. I've labeled columns B thru F as Monday thru Friday and I want to split the list into blocks for those days.

Right now I have 54 items and I want my block size to be 12 items, which gives me 4 blocks of 12 and 1 block of 6 - can I make Excel do the splits for me?

View Replies!   View Related
Splitting Data
The line below was originally a merged cell. I need to have the customer ID, Customer name, City, State and zip in seperate lines. I tried using the text to column, but the problem is some companies have a comma after their name to show if it is an llc or inc etc. What happens is the companies that do not have inc etc- it works just fine, however the ones that do have inc etc because there is another comma- it moves everything over one column. In the city column it says inc and in the state it has the city etc. Is there an easy way to make this work?

View Replies!   View Related
String Splitting
If I have a string which contains 3 words, is there an easy way of separating out the consonants into one string and the vowels into another (spaces should be ignored)?

The only way I can think of doing it is to go through the string item by item and comparing the letters to a list of vowels and using that to do the separation.

View Replies!   View Related
Splitting An Array
I have a spreadsheet I'm designing which requires me to take a series of numbers and return them lowest to highest. That's the easy bit.

The problem is where and how these numbers are displayed. They are not listed easily in two columns with the numbers in one column and someone's name against each one, but rather in four columns mapping out the odds and evens.

For example
1 Bob 2 Keith
3 Linda 4 Martin

Now obviously it'd be easiest if I could just re-format things into two columns, but that's not an option. However I'm finding myself a bit stuck when it comes to using an array in a formula.

I still want to return the names next to number, but I'm not sure how to have what's effectively a multiple array (or search two arrays at the same time). I'm looking to search the cells A1:A2 and C1:C2 in the example above, but not the cells B1:B2.

I just need to take the numbers in those cells A1:A2 and C1:C2 and list them in order highest to lowest.

View Replies!   View Related
Splitting Averages...
If I have a varying number of figures(say between 4 and 10) which I want the average of the first half of the set and an average of the second half, is there a function which I can use to calculate this? ie if there is 8 numbers in total then I need the average of the first four and last four... but if there is ten figures total then I need the average of the first five and last five.

View Replies!   View Related
Splitting Text
I have text of varying length that I need to split up into three fields. I am using a vba code (kindly shown to me on a previous post) to split the first word, but in this particular macro I need to remove the last word. This is the code that I have at present which is displaying the first word.

Sub splitDescription()
Dim c As Range, t
For Each c In Range("e2:e" & Range("e" & Rows.Count).End(xlUp).Row)
t = Split(c)

c.Offset(, 1) = t(0)

End Sub

The data I am trying to split consists of descriptions of varying lengths and I need to remove the first word and the last word and keep the middle text. Below is an example


View Replies!   View Related
Splitting A Value At The Decimal Place...
I have the value 26.06 in cell A1, I want to have everything left of the decimal place in Cell B1 and everything right of the decimal place in Cell C1, is this possible. Bearing in mind A1 will be variable (ie it could be 159.65).

View Replies!   View Related
Splitting And Extracting Certain Info
I have a spreadsheet which contains addresses split randomly into several columns and need to extract the postcode for every row.


Above is a small example of 2 of the columns, most addresses are in 3 columns.

The postcode can appear at the front, end or anywhere in the address field. Depends on who typed it in. I need to somehow extract the postcode and have it in a separate column.

Text to columns wont work i think because the info isnt uniform, I've tried the search for the various postcode starting letters but still cannot extract the entire postcode.

View Replies!   View Related
Splitting A Cell Into Multiple
I'm currently working on a little project and at the moment it's my programmign skills letting me down lol, I'm litterally about to start pulling my hair out over not getting this to wokr. I extract data from my device which is in the following format.

View Replies!   View Related
Splitting String To Cells
I have question I need a macro which splits strings to multiple cells.
Here is how my data looks like.

Here how I want it:

PS: I prefer macro instead of formula. T

View Replies!   View Related
Budget Splitting Formula
I have to run payroll every two weeks, and part of the process involves calculating overtime for over 50 employees. The total amount of overtime hours for each employee is usually split on two or three budgets and after I calculate the budget splits, I have to enter these amounts into the payroll system. (The payroll system takes up to 2 decimal spaces.)

I have a spreadsheet set up for each employee, but what I've noticed is that the sum of the parts does not always equal the whole.

For example, Bob Goodworker worked 9 hours of overtime this pay period.

His budget split will be as follows:

Budget A (58.5%) = 5.27
Budget B (31.5%) = 2.84
Budget C (10%) = 0.90

However, 5.27 + 2.84 + .90 = 9.01

The payroll system only allows for numbers up to 2 decimal places.

View Replies!   View Related
Splitting One Column Into Many Other Columns
I need a macro which place chosen parts from one column into another column.

For example I have following text in column E:

Column E
Flugschneise {f}
(total) im Arsch [vulg.]
Geld und Gut
Pensum {n}

*chosen content not always at the end of a cell!

Now i need a macro which puts chosen content from Column E to Column F or Column G. For example I want that all {f}s should be deleted from column E and put into column E. Or I want all {n} to be deleted and placed into column F. Or I want all [vulg.] to be deleted from column E and put into column G. If column F or G already consist of text then the new text( {f} or {n} or [vulg.] ...) should be added to the existing text but separated through a separator like comma or semicolon

It means that the macro is each time fed with information about what to delete from which column and where to put it. It would be cool if a simple msg box would ask for the needed information.

View Replies!   View Related
Splitting A String With Split
I'm stumbling upon something I just can't figure out. I thought I was being very clever using the Split() function, and it did all work beautifully for a while.
I've got a list sort of like this:

Hello of World
Hello1 of Planet
And I want to be able to run the following code.

For Each u In units
d_type_a = Split(u.Value, " of")
Debug.Print (d_type_a(0))

Select Case u.Value
Case "Hello"
Case "Helloworld"
Case "Hello1"
End select

But it fails on the Debug.print with a "Subscript out of range, nr 9" error. The debug is just there because it doesn't want to work. The debug.print DOES out put "Helloworld" into the Immediate window but then still fails which sorta confuses me.
The best I can figure out is that it just doesn't quite like the string without an " of" in it.

View Replies!   View Related
Date Splitting Between Two Dates
I think I need macro to do this but not sure.

I have a variable start date in cell A1 and end date in cell B1.
Lets say, start = 04/01/07 in A1 / end = 3/31/10 in B1 (date format used = mm/dd/yy)

I am looking for a way to partition these out in subsequent columns as follows:

A2 and B2 would = 04/01/07 and 12/31/07 (this section ends at the end of the first year specified in cells A1 and B1).

A3 and B3 = 01/01/08 and 12/31/08 respectively

A4 and B4 = 01/01/09 and 12/31/09

A5 and B5 = 1/1/10 and 3/31/10

I would like to be able to enter any start/end date values into cells A1/B1 and have the cells directly underneath split these dates out chronologically as described above.

The number of populated cells in rows underneath would need to grow dynamically based on dates input in cells A1/B1.

I have been chatting up to many coworkers recently. Excel is becoming a big deal at my office. I hope to see them chekc this site out.

View Replies!   View Related
Splitting Character String
How do I split the below character or similar strings into 2 columns of 25 characters each without cutting a word off?


View Replies!   View Related
Splitting Data From A List
I have an address list that each address is in one cell. How can I seperate the address, city, state, and zip when they vary so much? I have included a sample from the list below.

PO BOX 45009 SALT LAKE CTY UT 84145-0009

View Replies!   View Related
Splitting Text Across Cells
I'm having trouble splitting some data.

I have account codes like 1201.170170 that I must split into two cells containing 1201 and 170170

When I use "left" and "right" functions, it cuts off the zero on the far right. Assuming base data in cell F2...

=Right(f2,6) returns .17017 rather than the intended 170170

I've tried formatting the cells as text, but that doesn't seem to work either.

View Replies!   View Related
Splitting An Excel Cell
Is there anyway of formatting an Excel cell so that it is split diagonally in half and you can write different text in each half?

i.e. a week/year cell in Cell A1 with Row 1 being the Year numbers and Column A being the month numbers

View Replies!   View Related
Splitting Cell Into Two Cells
I know how to use concatenate but is there anything that will do the opposite. I want to take one cell that has both a date and time in it, and make the date in one cell and the time in another.

View Replies!   View Related
Splitting Text Using MID, RIGHT Functions
I have some samples below and I need to split them up.
CritValsMtrx(1, 1) = 8850216
CritValsMtrx(1, 2) = 10
CritValsMtrx(2, 1) = 8850832
CritValsMtrx(2, 2) = 5

The MID function to split the text as below and it works.
MID(A1,1,FIND(" (",A1,1)-1) returns "CritValMtrx"

I have tried the RIGHT function to split the text to have the followings but it didn't work RIGHT(A1,1,FIND(",",A1,1)-1).
"2) = 10"
"1) = 8850832"
"2) = 5"

In a nut shell, all I need is the text from the comma to the end of the string.

View Replies!   View Related
Splitting Cells By Non-printable Characters
I've had a quick look at past posts and there is plenty on splitting cells but I haven't been able to find anything to help me with the following:

I've pulled address details from a database which have populated into a single field with each line separated by 2 'square' characters. I need to split each line into a separate cell for a mail merge. I've seen these characters before and always assumed they represent a space or 'return' and I believe is an ASCII character?

If I select a cursor in the cell the lines drop as they should with 1 square character left at the end of each line and it stays this way when I leave the cell.

I've tried CLEAN & TRIM functions; the first removes the characters, the second doesn't recognise the character at all. The most promising seems the 'Text to Columns' function, setting it as a delimited string, but I don't seem to be able to enter this particular character as the delimiter under the 'other' option.

View Replies!   View Related
Splitting Data From A Cell Into Different Cells
I have a column that has data with multiple values seperated by commas. I need to seperate out the values and add them to a new column.


Column A
good, cheap, fast

I would like them to be added to column B as:

View Replies!   View Related
Splitting Data By Sorted Ranges
How do I split a worksheet by sorted ranges and then save each range as a new workbook?

For example:

How would I split the data on the left into the ranges on the right of the image below?

View Replies!   View Related
Splitting Names Into 2 Separate Columns
I have two columns in my excel sheet, the first column has the Second name, First Name and the second column has the Date of birth.

Now what I'm after is two columns really, the first column should show the Furst name Second Name (space in between with no Comma) and the Second column should show the First Initial from the first name, the first initial from the second name and then the date of birth.

View Replies!   View Related
Splitting *.csv Files Into Batches
I have a number of csv files with 30,000 - 60,000 records.

I'm looking for some code which will split them into batches of 250 record batches.

Each file id named FY838A, FY838B, FY838C etc.

I need something which will open the file then copy the first row (Field names), open a new book and paste the headers.

Then cut the first 250 records, rows 2:251 and paste them into the new book.

I then need to save them as a *.csv

All are named BatchFY838A (then B, C, D for each one)

Here's the main bit I can't figure out:

I would like to create a destination folder for each file.

I already have a main folder:
say C:IncompInbox
but when each file is created I need to make a folder for it to sit in:

C:IncompInboxFY838AFY 1
C:IncompInboxFY838AFY 2
C:IncompInboxFY838AFY 3
C:IncompInboxFY838AFY 4

(I need to create the FY838@ folder then the destination ones after that)

Until there are no records left to batch. i.e. the code needs to stop when it's completed the list (each one has a different amount of records)

Additional. Along with this I have 3 Word.doc's which need to be placed in each of the above folders, can I do this at the same time)

The end result is to be 3 Word.doc's and a *.csv file in each folder, this is for a huge Mail Merge which is already coded, so each files content will be 4 documents with exactly the same names.

View Replies!   View Related
Tax Year Date Splitting
I have a date that is save as a date variable. Is there any quick functions that can be used in VBA that will get a date split by tax year.

For Example

If I was wanting to look at tax year 06 this would be from April 06 to March 07. The year would have to be determined by a entered year Input box.
After it has been split by year I then if it falls between the dates to split the date by Month. Is there any simple functions and comparitors I can use on a date defined variable.

View Replies!   View Related
Splitting Contents Of A Cell At A Semicolon
I have two lists of over 10,000 text messages each. Each text is in the first cell of the column, with a few random rows being skipped here and there. Within each cell is a lot of information. The information is divided by semicolons, though. So, it's something like. 123;ContactName;+123456790;Date Time;Message

I have two files like this, one incoming and one outgoing. I need a way to not only split the text into different cells (each piece in a separete column), but splice the two files together. I realize if I could section the text into the parts, I could just sort it, but I figured it's better to tell more than less, for the sake there might be an even simpler way of doing it.

View Replies!   View Related
Splitting Address Details Into Several Columns
I'm in need to split 1 column (address details) into several columns (for ex. address, city, postcode, country) I can't use the "Text to Column" function as some address will split into 5 part, some split into 6/7/8. Is there any way I could do this automatically as there're a lot of entries in my spreadsheet. Example of the data need to be split attached.

View Replies!   View Related
Continuous Chart Instead Of Splitting It Into Two Series
I have data on two rows and would like to have a continuous chart instead of splitting it into two series. see attached file.

View Replies!   View Related
Copy Rows From One File To Another.
Is it possible to press a button in "abc.xls" and have excel ask me which .xls to look in and which rows from that sheet that should be copied and pasted at the bottom of "abc.xls"

Rows to copy would be copied entirely and identified by physical review not by excel looking for a specific value in the row.

View Replies!   View Related
Splitting Screen Within Workbook To See Two Worksheets Simultaneously
I am working on a document at work with multiple worksheets. It would be most useful for me to be able to split the screen so that I can work on both worksheets at the same time without (a) having to switch back and forth, (b) modifying the workbook so that everything is on one sheet, or (c) opening the file twice and sizing the windows to my screen.

My goal is to be able to see the information on each sheet while modifying one sheet or the other.

View Replies!   View Related
Numbers Not Staying In Number Format After Splitting It
Here's my number 20512202250

The first 4 digits are the shop #
The last 7 digits are the part #

I had to split the number to separate shop and part
I used LEFT(a2, LEN(a2)-7) for the shop which gave me 2051
I used REPLACE (a2,1,4,"") to get the 2202250 part number

My next column is an ISNUMBER formula
When I do an ISNUMBER to 2202250, it's coming back FALSE like it's showing it NOT to be a number.

How do I get the 2202250 to show up as a number?
If I change it manually, the ISNUMBER formula works correctly and reads it as a number.

View Replies!   View Related
Splitting A Cell String Depending On Variables
On to what brought me here; I have a spreadsheet with a column containing an address, eg '12 SMITH ST SOMESUBURB NSW 2001'. I'm looking at splitting this cell in half after the street and moving the second half (suburb state postcode) into the next cell, which i've managed to do with a series on InStr statements.

Now the fun part is that not all these addresses are streets, some are avenues (AV or AVE), roads (RD), place (PL), drive (DR or DRV) etc. Initially i just did this:

View Replies!   View Related
Splitting Comma Delimited Numbers Into New Columns
I am using excel at the moment with a card playing program. using the excel sheet they provided the details of what cards are dealt are exported to the worksheet and there is a simple table like so

Player Cards
Player 1/ 24, 27, 16
Player2/ 1, 5

The information is fed through one number at a time as the cards are dealt for a total of three rounds sometimes it is only two rounds and are delimited by a comma all in the same column. I would like if possible to have these numbers appear in separate columns. that is

Card 1 / Cards 2 / Card 3
Player 1
Player 2

IS this possible. briefly i want this to happen so I can use the Vlookup function as the numbers that come through each stand for a card value but using Vlookup only the first number works and the following return an NA value as it is impossible as far as I know to have every possible combination represented in a table . If there is a way of tweaking Vlookup so it recognises the comma delimiter and in the vlookup column it will show all converted numbers then i'm all ears otherwise any help on how to split would be much appreciated. Quickly I did try using the text to columns function when i did this however in the new destination it showed only the first number and discontinued showing the others in the original as well. Additionally in this function the 'preview of selected data' does not show selected data but some sort of link =programme_name_card_gamecard_1 somethig like that. Sorry for the long one.

View Replies!   View Related
Splitting Numbers Separated By A Space Automatically?
i have a list of customer codes each seperated by a space. In the column next to them i have a percentage. (see attached file before tab). I would like to be able to split the cell of customer codes and transpose them so that there is one cell per customer code with the appropriate % applied (please see attached file after example tab).

At the minute a manual process is completed of sorting in length order, using the text to column function then copy paste and transpose, then copy the % in. Its quite manual process, is there an easier way?

View Replies!   View Related
Splitting Wrapped Cell Into Separated Cells
I tried too much to split one cell Containing more than one lineI need to put every line in different cell.

My small example can describe better ..

View Replies!   View Related
Function To Insert - In Cells Splitting Data
My cell contents looks like this.


I want it to look like this..


I know this can be done by formula but i dont know it. This info is in col B.

View Replies!   View Related
CSV File With More Than 65536 Rows
I have a CSV-file of 30 Megabyte. But if I open it in excel he says that he could not open the complete file?? The maximum rows of excel are 65536. Can I enlarge the number of rows that excel can handle or is there anybody with the same problem and wrote an program for it in excel, VB or maybe word??

View Replies!   View Related
Import Bottom X Rows Of File
Is it possible to use the import external data function in excel to import only the bottom 1000 rows from a database? Right now I've only been successful at importing all the data but I only want the last x number of rows. The database is currently an excel file.

View Replies!   View Related
Splitting A Long Column Of Data Into Separate Columns
i have a long column pair of data, each entry in its own cell:

10/5/20088:30:00 AM
10/5/20088:46:00 AM
10/5/20089:14:00 AM
10/5/200810:18:00 AM
10/5/200810:42:00 AM
11/5/20088:30:00 AM
11/5/20088:46:00 AM
11/5/20089:14:00 AM
11/5/200810:18:00 AM
11/5/200810:42:00 AM
12/5/20088:30:00 AM
12/5/20088:46:00 AM
12/5/20089:14:00 AM
12/5/200810:18:00 AM
12/5/200810:42:00 AM
13/5/20088:30:00 AM
13/5/20088:46:00 AM
13/5/20089:14:00 AM
13/5/200810:18:00 AM
13/5/200810:42:00 AM
14/5/20088:30:00 AM
14/5/20088:46:00 AM
14/5/20089:14:00 AM
14/5/200810:18:00 AM
14/5/200810:42:00 AM

how can i program a macro to 'split' this column according to date? please refer to the attached picture as an example. i know this is probably a simple question but please bear with me i'm still new to excel programming.

View Replies!   View Related
Splitting Housenumbers And Text (housenumber Extra's)
I have got a excel file which has housenumbers. I would like to use the file for importing in a program.

Unfortunatly the program wants housenumbers en extra's split.

The file I have looks like this:

Ordernummer Geadresseerde Straat Huisnummer Toevoeging Postcode Woonplaats

The file I want looks like this:

Ordernummer Geadresseerde Straat Huisnummer Toevoeging Postcode Woonplaats

View Replies!   View Related
Word Splitting For Camel Cased Words Using VBA
I have a list of words some Title cased, and some camelCased. Each cell contains one word. I need to write a macro which if encounters a cell with camelcased word like "ExcelMacros" or "excelMacros" would add new row and add "excel" and "macro" to their own cells.
Is looping each cell for the character code the way to solve this problem or is there a better way?

View Replies!   View Related
Splitting Large Numbers Into Groups (LATITUDE AND LONGITUDE)
I have a spreadsheet with a column of Longitude values like:

I want to somehow split the cell into new individual cells so it will be like this:

17 28 28.383

View Replies!   View Related
Import Text File With More Lines Than Rows
I have a several large delimited text files around 800,000+ lines. It cannot all be pasted into excel due to the 65,000 line limit. For my purposes, I only need about 1 in every 1,000 lines from the text file anyways. Is there a way to only bring in the lines I want? Here is the test file layout:.................

I only need the lines that start:


I tried running some macros that I found through searching the forums, but I couldn't get any to work.

View Replies!   View Related
Copyright 2005-08, All rights reserved