TextBetween() first occurrence and last occurrence

[#1]

How do I set the text between to find the first occurrence of a value and the last occurrence of another value?

TextBetween(FIELD,'(',')')

is what I am needing but I want it to get all the text between the first occurrence of the open parenthesis ( and the last occurrence of the closed parenthesis because the user could input  open and closed parenthesis in the text input.

TextBetween(FIELD,'(',')',SUBSTRINGCOUNT)  I tried using a substring count here but it relates to the first criteria for the open parenthesis not the last close parenthesis.

What is the best way to do this?

[sunny_talwar · Accepted solution]

May be like this:

=Mid(FieldName, Index(FieldName, '(', 1) + 1, Index(FieldName, ')', -1) - Index(FieldName, '(', 1)-1)

[#3]

Thank you!  This would probably work.  I will test it out. But after digging into the data of the model (built by another developer) I found that they did not need to use the closed Parenthesis as there is NO data after the final ')'.  I removed it and now get all the data:  TextBetween(FIELD,'(','')

[#4]

It worked perfectly.  I decided to use your code so the last ')  did not show in the text