Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jgarciaf106
New Contributor III

Extract Text between Characters

Hi Experts,

How can I extract text between parenthesis?

Here are some samples of what is on the data source

Test (Test):  Expected Result (Test)

Test (Test (Inherited)): Expected Result (Test (Inherited))

Test (Regular) (Test(Inherited)): Expected Result (Test(Inherited)) not considering (Regular)


Any Advice?

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Extract Text between Characters

may be try this?

Mid(Sample, IF(Index(Sample,'(') = 1, Index(Sample, '(',2),Index(Sample,'(')))  AS SampleTrimmed

View solution in original post

10 Replies
vishsaggi
Esteemed Contributor III

Re: Extract Text between Characters

Are these the only three combinations or any other combination of data?

Highlighted
jgarciaf106
New Contributor III

Re: Extract Text between Characters

Hi @Vishwarath Nagaraju

Yes, and another similar to the third on is (1) Test (Test) expected result (Test) , ignoring (1) Test

vishsaggi
Esteemed Contributor III

Re: Extract Text between Characters

Can you share some sample data to work on.

We can use TextBetween(Field, 'Test (', ')') but want to try on your actual data if possible.

chrismarlow
Valued Contributor

Re: Extract Text between Characters

To generalise is it the contents of the last set of parenthesis that you are after (i.e. the set that finish at the end of the text)?


Regards,

Chris.

jgarciaf106
New Contributor III

Re: Extract Text between Characters

As the data i need to extract is confidential

I Could not load the excel File

 

Sample
Orga Name 1 (Sample Name)
Orga Name 2 (Sample Name)
​Orga Name 3 (Sample Name (Inherited))
(UK) Orga Name 3 (Sample Name)
1, Orga Name 4 (Sample Name (Inherited))
1,Orga Name 6 (Sample Name(Inherited))
1, Orga Name 7 (Sample Name (Inherited))
1, Orga Name 9 (Sample Name (Inherited))
1, Orga Name 10. (Sample Name(Sample Name))
1, Orga Name 11( Sample Name (Sample Name))
1, Orga Name 12 (Sample Name (Inherited))
1, Orga Name 13 (Sample Name (Inherited))
1,Orga Name 14 (Sample Name(Inherited))
2, Orga Name 15 (Sample Name(Inherited))
2, Orga Name 16 (Sample Name(Inherited))
2, Orga Name 17 (Sample Name(Inherited))
2, Orga Name 18 (Sample Name (Inherited))
2, Orga Name 19 (Sample Name(Inherited))
jgarciaf106
New Contributor III

Re: Extract Text between Characters

Correct last Set of Parenthesis, including the ones inside the last set.

Sample:

Orga Name 3 (Sample Name (Inherited))


Expected Result:

(Sample Name (Inherited))

chrismarlow
Valued Contributor

Re: Extract Text between Characters

Going to need to count the number of )'s at the end (or at the position of the last one if there can be non bracketed text after it) then find the index of the equivalent opening bracket to return that portion of the original.

vishsaggi
Esteemed Contributor III

Re: Extract Text between Characters

may be try this?

Mid(Sample, IF(Index(Sample,'(') = 1, Index(Sample, '(',2),Index(Sample,'(')))  AS SampleTrimmed

View solution in original post

jgarciaf106
New Contributor III

Re: Extract Text between Characters

That worked absolutely great.

Thanks @Vishwarath Nagaraju