How to Extract a specific words/text from a cell into another cell-CodePudding

If in a cell there are few texts like, Hi, How are you, Hello. I want to show in another cell if that cell contains either Hi and/or Hello and display the exact text on another cell.

I'm using Google Sheets.

I'm a beginner and searched a lot about my question but didn't find any.

CodePudding user response：

Use search if you don't care about case sensitivity:

=TEXTJOIN(",",true,
 if(isnumber(search("hi",a2)),"hi",""), 
  if(isnumber(search("hello",a2)),"hello",""))

If case-sensative then:

=TEXTJOIN(",",true,
 if(isnumber(find("hi",A2)),"hi",""), 
  if(isnumber(FIND("hello",A2)),"hello",""))

CodePudding user response：

try:

=INDEX(BYROW(IFNA(REGEXEXTRACT(SPLIT(A2:A5, ", "), "(?i)hello|hi")), 
 LAMBDA(x, TEXTJOIN(", ", 1, x))))