How to use MS Excel 2007 to split a long column or list into smaller chunks, for later batch processing:
Real world scenarios: You have a simple but huge list that you want to parcel/email out in equal portions to various project participants. Or you are working with an old form-based system that can only process X amount of items at a time.
1. Get the excellent free ASAP Utilities plugin, install it in Excel. Note that you may need to enable it before its tab will appear (Top-left orb | Excel Options | Add-ins | Disabled Applications | ASAP + Go | Enable ASAP | OK)
2. Open a new sheet and paste your long list down into a single column.
3. In your new ASAP Utilities tab, click the Select button.
4. ASAP’s Columns and Rows | Select gives you a list of choices before it runs. Choose option 2 (“Conditional Row and Column Select…”) and then use the dialog box that appears. Here I’ve opted to have ASAP tell Excel to select every 25th cell…
No ‘Select’ button? Go: Options | Find and Run a Utility | Type ‘Select’ | Scroll down to “Conditional Row and Column Select…”.
5. Run Select, then exit the dialog box. The cells won’t immediately look like they’ve been selected. But if you Ctrl + C to copy them, then the familiar “marching ants” will reassuringly appear around the selected cells.
6. Now right-click your mouse anywhere inside your new group of selected of cells, and choose ASAP Utilities | option 18 “Insert before and/or after each cell in your selection…” In this new dialog choose “Insert after” and type {lf} to add a new blank line inside each of your selected cells.
7. Run the Insert process. It may take a minute to run, on a long list. Each selected cell will be given a double height by adding a line-break, thus…
If you just need to print out an Excel spreadsheet with each list-chunk separated by a space, perhaps so that your manager can easily read through the list in printed form, then you can leave the process there.
8. Some may now want to go further. When the whole column is selected and copied out to Notepad, you will see that the 25th, 50th, 75th etc cell will appear in quote marks “”, thus…
item 24
“item 25”
item 26
That’s kind of useful, but not really — since the primitive Notepad can’t handle multi-line search/replace.
However, simply paste the same list into the free open-source Notepad++ and the list copies as…
item 24
“item 25
”
item 26
9. That’s perfect. So now we just use Notepad++ to search all the “ occurrences and replace them with blanks. Then we have our list in chunks of 25 — each nicely separated by a blank line.
10. The neatly chunked list can now be pasted back into Excel, adding real blank cells between each chunked section. You might then add a comma to each blank cell, thus giving a basic comma-delimited .csv file for use with automated mailing-list software and similar.
Or the list can simply be saved out of Notepad++ as a plain .txt list, to work with manually — in clearly defined batches of 25 at a time.
