regexgoogle-sheetsgoogle-sheets-formulatext-extraction

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
Solution

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