1

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.

BigBen
  • 46,229
  • 7
  • 24
  • 40
MMsmithH
  • 323
  • 1
  • 8
  • You could make a column for each name (e.g. the Bob column contains Bob or nothing) and then another column where you concatenate those columns and if the result is empty put in Nobody. – maraca Feb 18 '22 at 15:23
  • You may also use a function in GAS, see [Multiple regex matches in Google Sheets formula](https://stackoverflow.com/a/62704859/3832970). It would look like `=ExtractAllRegex(A2, "\b(Bob|Cynthia|Dale)\b", 0, ", ")`. It is easy to adapt it to output `Nobody` upon no match, too, using `return matches.length > 0 ? [matches.join(separator)] : "Nobody";` – Wiktor Stribiżew Feb 18 '22 at 15:53

1 Answers1

3

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"))," ",", ")))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thanks anyone know if I can add an I experession to make formula recognize any case? Similar to this solution:https://stackoverflow.com/questions/24644243/google-spreadsheets-regex-case-insensitive-regexreplace/24645121 – MMsmithH Feb 18 '22 at 17:10
  • Seems this may work but would also be useful to extract out everything in lower case or upper case? `=INDEX(SUBSTITUTE(TRIM(REGEXREPLACE(A2:A,".*?\b((?i)Bob|Dale|Cynthia)\b|.*","$1 "))," ",", "))` – MMsmithH Feb 18 '22 at 17:15
  • @MMsmithH, if the answer helped and answered your question you are welcome to tick the checkmark to the left of it. – JvdV Feb 18 '22 at 18:24
  • How might I add to this formula, so that the Bob|Dale|Cynthia be replaced by another specified substitute such as Bob Last Name|Cynthia_V_Smith|Dale_is_Cool – MMsmithH Feb 22 '22 at 19:02
  • How might I add to this formula, so that the Bob|Dale|Cynthia be replaced by another specified substitute such as Bob Last Name|Cynthia_V_Smith|Dale_is_Cool? Substitute formula is used in this Google Sheet in the second tab is used to replace with specified values in spreadsheet using this formula SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,INDEX($E$3:$E$5,1),INDEX($F$3:$F$5,1)), INDEX($E$3:$E$5,2),INDEX($F$3:$F$5,2)),INDEX($E$3:$E$5,3),INDEX($F$3:$F$5,3)) – MMsmithH Feb 22 '22 at 19:07
  • @mmsmith, new questions get new posts on SO. – JvdV Feb 22 '22 at 19:11
  • https://stackoverflow.com/q/71227476/17835120 – MMsmithH Feb 22 '22 at 19:52