Search code examples
regexgoogle-sheetsgoogle-sheets-formula

Find ALL matching words in a cell with Nested Regex or other?

I want to identify if particular text can be found in a cell in Google Spreadsheets like this example:

Text Desired Output
Cynthia and Dale went to the store Cynthia, Dale
Cynthia went to the store Cynthia

Unfortunately using this code IF(REGEXMATCH(A2,"Bob"),"Bob",IF(REGEXMATCH(A2,"Cynthia"),"Cynthia",IF(REGEXMATCH(A2,"Dale"),"Dale","Nobody")))

Only returns the first REGEX match found, not multiple ones as I would like.

Any suggestions for modifying the formula would be helpful.

A spreadsheet can be found with the examples and code here: https://docs.google.com/spreadsheets/d/1YuyFdEbqgZaSL2IbyyalVLLXsXrIAuqtaxCTXLhSuEU/edit?usp=sharing

Thanks in adavance.

Solution

  • Here is one option:

    enter image description here

    Formula in C2:

    =INDEX(SUBSTITUTE(TRIM(REGEXREPLACE(A2:A,".*?\b(Bob|Dale|Cynthia)\b|.*","$1 "))," ",", "))

    This will spill down the column.

    If you need to fill the empty cells with 'Nobody', maybe an nested REGEXREPLACE() would work:

    enter image description here

    Formula in C2:

    =INDEX(IF(A2:A="","",REGEXREPLACE(SUBSTITUTE(TRIM(REGEXREPLACE(A2:A,".*?\b(Bob|Dale|Cynthia)\b|.*","$1 "))," ",", "),"^$","Nobody")))

    Or without a 2nd replace:

    =INDEX(IF(A2:A="","",SUBSTITUTE(TRIM(REGEXREPLACE(A2:A&" Nobody","(?:.*?\b(Bob|Dale|Cynthia)\b|^.*(Nobody)$|.+)","$1 $2"))," ",", ")))