CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulatext-extraction
Mark
Mark

Reputation: 347

Google Sheets: extract text between two characters

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

I've found the function REGEXEXTRACT and I got it to work, only when there is one character. But I can't get it to work with multiple characters.

Example

2020-02: Test Course (QAS)

I need to extract text after : and before (.

So it would just return

Test Course

Upvotes: 2

Views: 14605

Answers (2)

Erik Tyler
Erik Tyler

Reputation: 9345

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."

Upvotes: 2

MattKing
MattKing

Reputation: 7773

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.

Upvotes: 5

Related Questions