Search code examples
stringgoogle-sheetslambdagoogle-sheets-formulatextjoin

How to extract a specific words/text from a cell into another cell

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.

enter image description here

I'm using Google Sheets.

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

Solution

  • 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",""))

    enter image description here