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

1
  • 1
    Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Dec 1 '21 at 22:19
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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