Text to Columns in an Access Table
- Sean O'Shea
- Sep 5, 2018
- 1 min read
You can parse data in an Access table as you would with the Text to Columns tool in MS Excel using the split function in a query. See the VBA code posted to this site by sxschech.
Start with a table like this one, which has delimiters (such as ~) which you want to use as a reference to separate data to the left of delimiter into the new column at the right.

Insert this VBA code in a new module:
Option Compare Database
Function QuerySplit(FieldName As String, Delim As String, Position As Integer) QuerySplit = Split(FieldName, Delim)(Position) End Function

Create a new query and select the SQL view.

Enter these SQL commands:
SELECT Field1, Trim(querysplit([Field1],"~",0)) AS Field2 FROM Table1;
We select the field to be parsed:
SELECT Field1,
. . . trim by the delimiter:
Trim(querysplit([Field1],"~",0))
. . . designate the new field to hold the extracted data,
AS Field2
. . . and reference the table to run the query in:
FROM Table1;
Run the query and the data will be parsed:
