Extract Text Between Parentheses

[Purdue2479]

I have an expression below that I am trying to use to extract text from within parentheses in a field, but it is leaving the last ")" on the extracted text. Can anyone help me determine what I need to do to just return the text? Thanks.

The length of the field is 100.

Mid([SIDE_DRUG_DESC],InStr(1,[SIDE_DRUG_DESC],"(",1)+1,Len([SIDE_DRUG_DESC])-(InStr(1,[SIDE_DRUG_DESC],"(",1)+1))

 

[Bilbo_Baggins_Esq]

if the Length of [SIDE_DRUG_DESC] includes the open pren and the close pren, then the final section should be:

Len([SIDE_DRUG_DESC])-(InStr(1,[SIDE_DRUG_DESC],"(",1)+2)

 

[Brianwarnock]

I take it that the string you are parsing is the last info in the field?

In which case try

Mid([SIDE_DRUG_DESC],InStr(1,[SIDE_DRUG_DESC],"(",1)+1,Len([SIDE_DRUG_DESC])-(InStr(1,[SIDE_DRUG_DESC],"(",1)))

Its actually, but lets not be silly

Mid([SIDE_DRUG_DESC],InStr(1,[SIDE_DRUG_DESC],"(",1)+1,Len([SIDE_DRUG_DESC])-(InStr(1,[SIDE_DRUG_DESC],"(",1)+1))-1

Brian

I see that i've got my brackets wron so ignore this. I would go with Bilbo's next post

 

[Bilbo_Baggins_Esq]

Actually, if it were me, I would keep track of both the "(" and the ")".

as follows:
Mid([SIDE_DRUG_DESC],InStr(1,[SIDE_DRUG_DESC],"(")+1,InStr(1,[SIDE_DRUG_DESC],")")-InStr(1,[SIDE_DRUG_DESC],"(")-1)

This method allows for varing lengths of information before and after the "(" and the ")".