topic Re: Extract Text between Characters in QlikView

Extract Text between Characters

jgarciaf106 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Extract Text between Characters

vishsaggi — Tue, 14 Aug 2018 14:41:30 GMT

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

Re: Extract Text between Characters

jgarciaf106 — Tue, 14 Aug 2018 14:44:25 GMT

Hi @Vishwarath Nagaraju

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

Re: Extract Text between Characters

vishsaggi — Tue, 14 Aug 2018 14:47:37 GMT

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.

Re: Extract Text between Characters

chrismarlow — Tue, 14 Aug 2018 15:05:23 GMT

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.

Re: Extract Text between Characters

jgarciaf106 — Tue, 14 Aug 2018 15:06:14 GMT

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）)

Re: Extract Text between Characters

jgarciaf106 — Tue, 14 Aug 2018 15:08:05 GMT

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

Sample:

Orga Name 3 (Sample Name (Inherited))

Expected Result:

(Sample Name (Inherited))

Re: Extract Text between Characters

chrismarlow — Tue, 14 Aug 2018 15:17:37 GMT

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.

Re: Extract Text between Characters

vishsaggi — Tue, 14 Aug 2018 16:31:50 GMT

may be try this?

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

Re: Extract Text between Characters

jgarciaf106 — Tue, 14 Aug 2018 16:35:51 GMT

That worked absolutely great.

Thanks @Vishwarath Nagaraju

Re: Extract Text between Characters

vishsaggi — Tue, 14 Aug 2018 16:38:04 GMT

Great. Please close the thread marking correct /helpful responses so that others can be helpful.