Parsing Data Out by Delimiters In Access - The Access Text to Columns Alternative

The text to column tool in Excel 2010 is great for parsing out data by delimiters, but there is a maximum of 1 048 576 rows for any one spreadsheet. An Access database can hold an unlimited number of records so long as the file size does not exceed 2 GB. If you have data that you want to parse out in Access follow these steps. [see the numbered screen grabs below for each step].

1. In this example we begin with a table holding text in one column for four different fields that we want moved into separate columns.

2. Remove the labels for the data, and then add in markers at the beginning of the fields to be separated, perhaps with a tilde and then one letter as a signifier.

3. Go into Query Design and add the Table. Add the column containing the text to be parsed. Then add in successive Instr functions to get the location of each of the delimiters. They should be in this format:

Parse1: InStr([Field1],"~l")

4. After running the query, you'll see that the Instr functions will have marked the point in the original column where each delimiter begins.

5. Now enter a function in the below format for each field to collect the data you want in different columns. The Mid function is set to start two characters after where the Parse function that we created marks the beginning of the first field. Then after the comma, it is set for a number of characters equal to the start position of the second field minus the start position of the first field, back two characters further to take off the actual delimiter maker:

Loan: Mid([Field1],[Parse1]+2,([Parse2]-[Parse1])-2)

. . . modify the code for the last column this way:

Original Principal Balance: Mid([Field1],[Parse4]+2,(Len([Field1])-[Parse3])-2)

The Len function calculates the entire length of the field the text is being pulled from.

6. Run the query - the big red exclamation point on the top toolbar, and you'll have the data in four different columns.