this function is close to what you want, you will need to finish it off
Code:
Function listKeywords(rData As String) As String
Dim r As String
Dim a() As String
Dim i As Integer
Dim f As String
'assumption keywords are preceded with a space and end with a space or other character
'standardise other characters to spaces
r = Replace(rData, ";", " ")
r = Replace(r, "!", " ")
r = Replace(r, ":", " ")
r = Replace(r, ",", " ")
r = Replace(r, Chr(10), " ")
r = Replace(r, Chr(13), " ")
'etc
'remove double spaces
While InStr(r, " ") <> 0
r = Replace(r, " ", " ")
Wend
'pass to array
a = Split(r, " ")
'loop through array to find keywords
For i = 0 To UBound(a) - 1
If InStr(a(i), "_") <> 0 Then 'this is a keyword
If InStr("; " & f, "; " & a(i)) = 0 Then f = f & "; " & a(i) 'add if not already added
End If
Next i
listKeywords = Mid(f, 3)
End Function
call it in a query
SELECT RawData.[Raw Data], listKeywords([raw data]) AS Expr1
FROM RawData
these are the results - equivalent to column C
Expr1 |
sort_number; foreign_key; 'first_line_supervisor'; field_name; is_null; pers_employment_status1_id; pers_employment_status2_id; (pers_factor_flag; pers_operator_flag; pers_injured_flag; ((pers_1st_line_supervisor; pers_id; A1st_line; name/AFSASDATA.PERSON.PERS_1ST_LINE_SUPERVISOR |
sort_number; foreign_key; 'one_liner'; field_name; is_null; (mshp_oneliner; mshp_id |
PERS_DAYS_ON_QUARTERS |
you can see the issues - some words have been combined, (e.g. is_nullFROM), some still have other characters such as ( etc, you just need to find all the characters you want to ignore and include in the replace section - but be aware if these characters form part of a key word, you will have a problem