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)
Create a new query and select the SQL view.
Enter these SQL commands:
SELECT Field1, Trim(querysplit([Field1],"~",0)) AS Field2
We select the field to be parsed:
. . . trim by the delimiter:
. . . designate the new field to hold the extracted data,
. . . and reference the table to run the query in:
Run the query and the data will be parsed: