Cut One String Into Multiple Strings

[BusyProfessorFromBremen]

Hey @ all,

at the moment there is one table existing which contains one big string in
one column. I would like to cut this string into its components and by doing
so generate smaller strings. These smaller strings should be copied into
another table where the colums heading stand for one string. Please see below.

State of the art:

ID STRINGCOLUMN
123 First, Last ; Second, Third; Forth, Fifth
321 First, Last ; Seventh, Third; Sixth, Fifth

Objective:

ID STRING1 STRING2 STRING3
123 First, Last Second, Third Forth, Fifth
321 First, Last Seventh, Third Sixth, Fifth

Any help will be greatly apreciated! Thank you very much in advance.

malte

 

[John Spencer MVP]

You can use a custom VBA function like the one posted below to get the parts.

In your query you would have three calculated fields that would look like

Field: String1: getSection([StringColumn,";",1)

You could add the Trim function to that to strip off any leading or trailing
spaces.

Field: String1: Trim(getSection([StringColumn,";",1))

Copy the following into a VBA module and save the module with a name other
than getSection.

Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' Access 2000 and later
'==============================================================

Dim strArray As Variant

If Len(strIn & vbNullString) = 0 Then
getSection = strIn
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)

If UBound(strArray) >= intSectionNumber - 1 Then
getSection = strArray(intSectionNumber - 1)
Else
getSection = Null
End If

End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County