Menu
To extract the leftmost characters from a string, use the LEFT function in Excel. To extract a substring (of any length) before the dash, add the FIND function. Explanation: the FIND function finds the position of the dash. Subtract 1 from this result to extract the correct number of leftmost characters.
To check to see if a cell contains more than one substring, you can use a formula based on the COUNTIF function. The core of this formula is COUNTIF, which returns zero if none of the substrings is found, and a positive number if at least one substring is found.
You can change both based on your needs. (2) If you want to find the specific string from nth character in a cell, says the 10th character, you can adjust the formula as =FIND( "KTE",A2, 10). (3) Both formulas =FIND( "KTE",A2) and =FIND( "KTE",A2, 10) are case-sensitive.
Try using this:
=ISNUMBER(SEARCH("Some Text", A3))
This will return TRUE if cell A3 contains Some Text.
The following formula determines if the text "CHECK" appears in cell C10. If it does not, the result is blank. If it does, the result is the work "CHECK".
=IF(ISERROR(FIND("CHECK",C10,1)),"","CHECK")
For those who would like to do this using a single function inside the IF statement, I use
=IF(COUNTIF(A1,"*TEXT*"),TrueValue,FalseValue)
to see if the substring TEXT is in cell A1
[NOTE: TEXT needs to have asterisks around it]
This formula seems more intuitive to me:
=SUBSTITUTE(A1,"SomeText","") <> A1
this returns TRUE if "SomeText" is contained within A1.
The IsNumber/Search and IsError/Find formulas mentioned in the other answers certainly do work, but I always find myself needing to look at the help or experimenting in Excel too often with those ones.
Check out the FIND() function in Excel.
Syntax:
FIND( substring, string, [start_position])
Returns #VALUE! if it doesn't find the substring.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With