Search code examples
regexgoogle-sheetsre2

Extract only ALLCAPS words with regex

Looking for a way to extract only words that are in ALL CAPS from a text string. The catch is that it shouldn't extract other words in the text string that are mixed case.

For example, how do I use regex to extract KENTUCKY from the following sentence:

There Are Many Options in KENTUCKY

I'm trying to do this using regexextract() in Google Sheets, which uses RE2.

Looking forward to hearing your thoughts.

Solution

  • Pretending that your text is in cell A2:

    If there is only one instance in each text segment this will work:

    =REGEXEXTRACT(A2,"([A-Z]{2,})")

    If there are multiple instances in a single text segment then use this, it will dynamically adjust the regex to extract every occurrance for you: 

    =REGEXEXTRACT(A2, REPT(".* ([A-Z]{2,})", COUNTA(SPLIT(REGEXREPLACE(A2,"([A-Z]{2,})","$"),"$"))-1))