Extract a text between two last parentheses

[Silvio]

Hello, I need help in writing a statement to extract the text between two
parentheses from the first open parentheses starting from the right of the
string.

Example:
This is just a test (Hello)
This is just a test (Hello There)
This is just a test (nice day) (Again)

From the example above the extraction will be:
Hello
Hello There
Again


This is what I have but it does not work properly (it extract the
parentheses and other text with parentheses not just the last one):
Right([Program Element],InStr([Program Element],"(")+1)

Thanks!

 

[fredg]

Hello, I need help in writing a statement to extract the text between two
parentheses from the first open parentheses starting from the right of the
string.

Example:
This is just a test (Hello)
This is just a test (Hello There)
This is just a test (nice day) (Again)

From the example above the extraction will be:
Hello
Hello There
Again

This is what I have but it does not work properly (it extract the
parentheses and other text with parentheses not just the last one):
Right([Program Element],InStr([Program Element],"(")+1)

Thanks!

There are several methods.
If your Access version includes the InStrRev() and Replace()
functions, here is one method.

NewText:Replace(Mid([Program Element],InStrRev([Program
Element],"(")+1),")","")

 

[Silvio]

Thanks Fred, that works just fine.

Hello, I need help in writing a statement to extract the text between two
parentheses from the first open parentheses starting from the right of the
string.

Example:
This is just a test (Hello)
This is just a test (Hello There)
This is just a test (nice day) (Again)

From the example above the extraction will be:
Hello
Hello There
Again

This is what I have but it does not work properly (it extract the
parentheses and other text with parentheses not just the last one):
Right([Program Element],InStr([Program Element],"(")+1)

Thanks!

There are several methods.
If your Access version includes the InStrRev() and Replace()
functions, here is one method.

NewText:Replace(Mid([Program Element],InStrRev([Program
Element],"(")+1),")","")

 

[vanderghast]

You can take the last element of the array in the result of a Split.

? Split( "Hello(whole)(world)", "(" ) (2)
world)


Note that it can return more than the last parenthesis:

? Split( "Hello(all(world))", "(" ) (2)
world))

since the last ( was included inside another unclosed one.

And you can have the number of "(" with:

? len(initialString) - len( Replace(initialString, "(", vbNullString))



Vanderghast, Access MVP