Extract String Between Last 2 Parantheses

I use the formula below to pull out all the data between parentheses:

REGEX_Replace([Course Title], ".*$(.*)$.*", "$1")

Now I need to get only the data between the last two parentheses. See below, some of my original data has more than 2 parentheses, but the data I need will always be between the last two:

Original Data	Data I Need
J (Q.) Smith ( QWERTY )	QWERTY
B (D.) Jones (ASDFGH)	ASDFGH
R Johnson ( ZXCVBN45 )	ZXCVBN45
P COLLINS ( LKJHGF )	LKJHGF
Z Brownfox ( DFGH234 )	DFGH234

Preparation

Accepted answers

Deano478

Hey @Rob48 you can use this RegEx

REGEX_Replace([Original Data], '.*\((.*?)\).*', '$1')

Then use a data cleanse to remove any extra whitespaces

ed_hayter

DataNath with it very fast - GOAT contributer.

I did it with a Regex-Parse and the expression:

.*$(.*)$$

Mine requires a separate formula to trim so I like it less than the replace.

All comments

DataNath

Hey @Rob48, there's a few expressions you could use for this - I've gone with something like this (using the Formula tool so you can also trim very easily, but have also left the RegEx tool option for you to see):

Trim(REGEX_Replace([Original Data], '.*\(([^)]+).*', '$1'))

Parse data between last 2 parentheses.yxmd

Deano478

Hey @Rob48 you can use this RegEx

REGEX_Replace([Original Data], '.*\((.*?)\).*', '$1')

Then use a data cleanse to remove any extra whitespaces

ShankerV

Hi @Rob48

One way of doing this.

Screenshot 2023-11-08 163539.png

$\s{0,1}(\w+)\s{0,1}$

Many thanks

Shanker V

ed_hayter

DataNath with it very fast - GOAT contributer.

I did it with a Regex-Parse and the expression:

.*$(.*)$$

Mine requires a separate formula to trim so I like it less than the replace.

JamesCharnley

Hi @Rob48

Put together a solution with string functions just simply because it's possible.

RSParse.yxmd

Rob48

Thanks DataNath the formula worked.

One more question if you don't mind. I have a hard time following RegEx logic, can you point out where in the formula the two parentheses are ID'd and the data is extracted? I'd like to keep this formula in my bag of tricks if I need it for other special characters or strings.

Trim(REGEX_Replace([Original Data], '.*\(([^)]+).*', '$1'))

Thanks!

DataNath

Glad to hear that @Rob48 - the breakdown is as follows:

.*\( - Takes as many characters as necessary to get to the last occurrence of '(',

( - Starts the capture group

[^)]+ - Square brackets create a character lookup group, adding ^ to the start denotes 'is not a member of this group' and this part therefore spans one or more characters that aren't ')' i.e. everything until we hit ')'

) - End the capture group

.* - Rest of string

Rob48

trying to do this again with different special characters but i'm lost - I can't figure out where my two parentheses are the represent the second to last and the last iteration. Can you recopy the formula but replace the target characters with "###"?

so that with data that looks like this:

ABC###XYZ###1234###QWERTY###THE ANSWER###ASDFASDF

will return:

THE ANSWER

(I'm assuming the formula would be the same whether it's a single # or a string of 3)

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703