topic Re: TextBetween() first occurrence and last occurrence in QlikView

TextBetween() first occurrence and last occurrence

Fri, 03 Feb 2017 15:52:25 GMT

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?

Re: TextBetween() first occurance and last occurance

sunny_talwar — Fri, 03 Feb 2017 15:58:13 GMT

May be like this:

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

Re: TextBetween() first occurance and last occurance

Fri, 03 Feb 2017 16:47:45 GMT

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,'(','')

Re: TextBetween() first occurrence and last occurrence

Fri, 03 Feb 2017 17:04:16 GMT

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