Extract a string from a field using complex rules

[Jubinell]

Hi,

I have a dictionary table with a definition text field. This field is of the following format:

(c-1) (c-2) ... (c-nth) definition

Each line always starts with at least one category keyword in parentheses, separated by a single space, followed the the definition of the word.
For example:

(comp) restart, reboot
(n, v) to chase
(adj) (seemingly) defeatable
(exp) to verify (a statement), to warrant

As you can see, the problem here is that the definition may or may not contain texts with parentheses and the latter may or may not be separated from the rest of the definition with a space.

Objective:
I would like to create new fields, category1, category2, etc. to capture the category keywords in parentheses associated with each word entry. For simplicity's sake let's assume that the definitions do NOT start with some text in a parenthesis (although I'd appreciated your thoughts on how to tackle this problem as well), my query/macro will return the following results for the four definitions above:

cat1 | cat2 | cat3
n | comp
n, v
adj | seemingly
exp

Any ideas?

 

[SydneyGeek]

Here's one approach:
This code goes in a new module (Alt+F11, Insert>Module, then Paste the code and save the module as basParse).

Function ParseTerms(strIn As String, intPart As Integer)
    Dim strText As String
    Dim intLen As Integer
    Dim x
    Dim i As Integer
    
    'grab just the elements we need.
    strText = Replace(strIn, ") (", "|")
    strText = Left(strText, InStr(1, strText, ")") - 1)
    strText = Mid(strText, 2)
    
    'separate out the categories
    x = Split(strText, "|")
    If intPart > UBound(x) + 1 Then
        ParseTerms = ""
    Else
        ParseTerms = x(intPart - 1)
    End If
End Function

Assuming that you have tblDictionary with a field called Term, you would extract the first term using an expression like
Cat1:ParseTerms([Term],1)

Repeat for as many terms as you think you will have. I did a test using 4 Cat fields -- result is:

Excel Workbook
	A	B	C	D	E
10	Term	Cat1	Cat2	Cat3	Cat4
11	(n) (comp) restart, reboot	n	comp
12	(n, v) to chase	n, v
13	(adj) (seemingly) defeatable	adj	seemingly
14	(exp) to verify (a statement), to warrant	exp
Sheet

Denis

 

[Jubinell]

Thanks Denis...let me try that out.