0

I am working with a Google Sheet where column A contains a set of values. Every value contains the following string "SAME_WORD LETTER", where LETTER is one of the alphabet letters, like "SAME_WORD A", "SAME_WORD B", "SAME_WORD C", etc. The first letter in SAME_WORD is always capital, the string may be preceded and followed by either a space or a symbol (like a dash) but never by another letter or number.

The following formula does what I need for A, B, and C but it's very long with all the alphabet letters

=ArrayFormula(IF(REGEXMATCH(A:A,"\b SAME_WORD A(.*)"),"SAME_WORD A",IF(REGEXMATCH(A:A,"\b SAME_WORD B(.*)"),"SAME_WORD B",IF(REGEXMATCH(A:A,"\b SAME_WORD C(.*)"),"SAME_WORD C"))))

I would like to improve efficiency and learn something so my request is to modify my formula to remove the conditionals and use something else instead, like REGEXEXTRACT.

TIA

Improve this question
1
  • 1
    Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there.
    – doubleunary
    Commented Dec 1, 2021 at 22:19

1 Answer 1

Reset to default
1

Use regexextract() like this:

=arrayformula( iferror( regexextract(A1:A, "(?:\b|\s)(SAME_WORD [A-Z])") ) )

To learn the exact regular expression syntax used by Google Sheets, see RE2.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.