ISNUMBER(SEARCH(substring,text))
ISNUMBER(SEARCH(C5,B5))
Case sensitive version
ISNUMBER(FIND(substring,text))
If cell contains
IF(ISNUMBER(SEARCH(substring,text)), "Yes", "No")
With hardcoded search string
ISNUMBER(SEARCH("apple",A1))
References
https://exceljet.net/formula/cell-contains-specific-text