How to do "text to column" in Access?

[Joycey]

I know there is a "text to column" function in Excel, how do I do the same
with Access?

I have a database with 700000 rows of data, so export to excel then import
it back is not an option.

The particular field I need to split looks like this
(xxxxxxxxxxx) and (yyyyyyyyyy) and (zzzzzzzzzzz)
(aaaaaaaaaaa) and (bbbbbbb) and (ccccccccccccccc)

The number of characters inside the brackets differ from row to row.

Thanks.

 

[Albert D. Kallal]

How man "and" sets of data will there be? Is it consitanct, or does that
change also?

The follwing function will put out a value from a string:

Public Function strToken(strData As Variant, intToken) As Variant

Dim vArr As Variant
Dim strBuf As String

If IsNull(strData) Then Exit Function

vArr = Split(strData, "(")

If intToken <= UBound(vArr) Then
strBuf = vArr(intToken)
If Len(strBuf) > 0 Then
strBuf = Split(strBuf, ")")(0)
strToken = strBuf
End If
End If


End Function


So, if we have

" (abc) and (def) and (ghi)"

Then in the debug window, we have:

? strtoken("(abc) and (def)",1)

You get abc
and

? strtoken("(abc) and (def)",2)
you get def

The only question now is determining how many "sets" of values you can have.
You would then create the first column, and then run a update query, like:

update tblMain set newCollum1 = strToken([dataCollum],1)

However, it very likely you should normalize each value out to a new table,
and not actually create a new column (unless the data has a 'fixed' number
of columns).