Forum Discussion

miked2525's avatar
miked2525
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" that the value $1 is returned, but if it finds "orange" that the value is $2 and if neither word is in cell C3 that a blank is returned. =IF(OR(ISNUMBER(SEARCH("apple",C3)),ISNUMBER(SEARCH("apple",C3))),"1","")

excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Steel 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,"")
    • miked2525's avatar
      miked2525
      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's avatar
        m_tarler
        Steel 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.

Resources