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:
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:
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"))," ",", ")))
CodePudding user response：
This discussion has been modified to include another element and has been reasked over here:
Using RegEx in Google Sheets to replace all matching words in string with another word or phrase