I have a field where I need to extract the text between two characters.

I've found regexextract and I got it to work when there is one character but I can't for the life get it to work with multiple characters.

2020-02: Test Course (QAS)

I need to extract text after : and before (

So it would just return "Test Course"

TYIA

CodePudding user response：

If it's for just one cell (say A2):

=IFERROR(TRIM(REGEXEXTRACT(A2,":([^\(] )")))

This will return what you want regardless of spaces after the colon or before the opening parenthesis. If no match is found, null will be returned.

If it's to process an entire range (say, A2:A), place the following in, say, B2 of an otherwise empty Col B:

=ArrayFormula(IF(A2:A="",,IFERROR(TRIM(REGEXEXTRACT(A2:A,":([^\(] )")),A2:A)))

This will return what you want regardless of spaces after the colon or before the opening parenthesis. If no match is found, the original string will be returned.

In both cases, the REGEX string...

:([^\(] )

... means "a grouping of any number of characters that aren't an opening parenthesis and which follows a colon."

CodePudding user response：

One way to do that would be with the INDEX() and SPLIT() functions like this:

=TRIM(INDEX(SPLIT(A2,":("),2)

Split splits the text into 3 parts using the : and (, then INDEX chooses the second part.

The TRIM() just gets rid of the spaces.