Text to Columns in an Access Table

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: