Extract Text between Characters

[jgarciaf106]

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?

[vishsaggi]

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

[jgarciaf106]

Hi @Vishwarath Nagaraju

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

[vishsaggi]

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]

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]

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]

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]

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 · Accepted solution]

may be try this?

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

[jgarciaf106]

That worked absolutely great.

Thanks @Vishwarath Nagaraju