Forum Discussion

Copper Contributor

Oct 31, 2024

Search for text within a cell and returning value based on what is found or leave the cell blank

I need to Search for text within a cell and returning value based on what is found or leave the cell blank if neither is found. Hello, I need to amend this example formula so if cell C3 finds "apple"...

Formulas and Functions

Bronze Contributor

Oct 31, 2024

=IF(ISNUMBER(SEARCH("apple",C3)),"$1",IF(ISNUMBER(SEARCH("orange",C3)),"$2",""))
OR
=IFS(ISNUMBER(SEARCH("apple",C3)),"$1",ISNUMBER(SEARCH("orange",C3)),"$2",TRUE,"")

Copper Contributor

Oct 31, 2024

Thank you! How do I add to this formula logic to return a blank (or $0) if neither apple or orange are a word in cell C3? m_tarler

m_tarler
Bronze Contributor
Oct 31, 2024
That formula already returns "" (blank) if neither is found. HOWEVER "" is NOT truly BLANK in the sense if you try using ISBLANK() it will return FALSE since that cell may look blank it has a formula in it and returns an empty string value.
- miked2525
  Copper Contributor
  Nov 01, 2024
  m_tarler
  Hi!
  What did I do wrong here? I'm looking to return one of these values based on cell V3 containing one of these words OR return a " " (blank) if none of these words are present in cell V3. I'm getting a "
  =IF(OR(ISNUMBER(SEARCH("apple",V3)),"$1","",ISNUMBER(SEARCH("orange",V3))),"$2","",(ISNUMBER(SEARCH("banana",V3)),"$3","",ISNUMBER(SEARCH("grape",V3))),"4","")
  - miked2525
    Copper Contributor
    Nov 01, 2024
    @m_tarler
    Hi!
    What did I do wrong here? I'm looking to return one of these values based on cell V3 containing one of these words OR return a " " (blank) if none of these words are present in cell V3. I'm getting a "
    =IF(OR(ISNUMBER(SEARCH("apple",V3)),"$1","",ISNUMBER(SEARCH("orange",V3))),"$2","",(ISNUMBER(SEARCH("banana",V3)),"$3","",ISNUMBER(SEARCH("grape",V3))),"4","")

Resources