Extract Data from a Field using a delimiter character

[Guest]

I have a column called Fluid Code with the following values:

P (Process Piping)
AA (Aeration Air)
AI (Instrument Air)
SCL (LP Steam Condensate)

I need to extract the value inside the parenthesis only so my query returns
Process Piping or Aeration Air or Instrument Air. How can that be
accomplished. I though using the Left ( ) function but since the length of
the text is variable I am not sure how to use that function using the
parenthesis as delimiter. Thanks in advance for your help.
Bruno

 

[John Vinson]

I have a column called Fluid Code with the following values:


I need to extract the value inside the parenthesis only so my query returns
Process Piping or Aeration Air or Instrument Air. How can that be
accomplished. I though using the Left ( ) function but since the length of
the text is variable I am not sure how to use that function using the
parenthesis as delimiter. Thanks in advance for your help.
Bruno

Use InStr() to find the position of the parenthesis:

Mid([Fluid Code], InStr([Fluid Code], "(") + 1, InStr(InStr([Fluid
Code], "(") + 1, [Fluid Code], ")"))

Air code, untested - you may need to play around with the nested
InStr's, which are intended to find the length of the parentesiszed
string.

John W. Vinson[MVP]

 

[Guest]

Thanks John, as with the other answers I received yours worked like a charm,
thanks!.

I have a column called Fluid Code with the following values:


I need to extract the value inside the parenthesis only so my query returns
Process Piping or Aeration Air or Instrument Air. How can that be
accomplished. I though using the Left ( ) function but since the length of
the text is variable I am not sure how to use that function using the
parenthesis as delimiter. Thanks in advance for your help.
Bruno

Use InStr() to find the position of the parenthesis:

Mid([Fluid Code], InStr([Fluid Code], "(") + 1, InStr(InStr([Fluid
Code], "(") + 1, [Fluid Code], ")"))

Air code, untested - you may need to play around with the nested
InStr's, which are intended to find the length of the parentesiszed
string.

John W. Vinson[MVP]