Check whether a cell contains a substring

Question

Is there an in-built function to check if a cell contains a given character substring     It would mean you can apply textual functions like Left Right Mid on a conditional basis without throwing errors when delimiting characters are absent

User · Answer

This is an old question but a solution for those using Excel 2016 or newer is you can remove the need for nested if structures by using the new IFS( condition1, return1 [,condition2, return2] ...) conditional.

I have formatted it to make it visually clearer on how to use it for the case of this question:

=IFS(
ISERROR(SEARCH("String1",A1))=FALSE,"Something1",
ISERROR(SEARCH("String2",A1))=FALSE,"Something2",
ISERROR(SEARCH("String3",A1))=FALSE,"Something3"
)

Since SEARCH returns an error if a string is not found I wrapped it with an ISERROR(...)=FALSE to check for truth and then return the value wanted. It would be great if SEARCH returned 0 instead of an error for readability, but thats just how it works unfortunately.

Another note of importance is that IFS will return the match that it finds first and thus ordering is important. For example if my strings were Surf, Surfing, Surfs as String1,String2,String3 above and my cells string was Surfing it would match on the first term instead of the second because of the substring being Surf. Thus common denominators need to be last in the list. My IFS would need to be ordered Surfing, Surfs, Surf to work correctly (swapping Surfing and Surfs would also work in this simple example), but Surf would need to be last.

User · Answer

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

User · Answer

Here is the formula I m using   IF  ISNUMBER FIND     A1    LEN A1  - FIND     A1   0

User · Answer

It s an old question but I think it is still valid   Since there is no CONTAINS function  why not declare it in VBA  The code below uses the VBA Instr function  which looks for a substring in a string  It returns 0 when the string is not found   Public Function CONTAINS TextString As String  SubString As String  As Integer     CONTAINS   InStr 1  TextString  SubString  End Function

User · Answer

I like Rink Attendant 6 answer   I actually want to check for multiple strings and did it this way   First the situation  Names that can be home builders or community names and I need to bucket the builders as one group   To do this I am looking for the word  builder  or  construction   etc   So -    IF OR COUNTIF A1   builder    COUNTIF A1   builder      Builder   Community

User · Answer

Try using this    ISNUMBER SEARCH  Some Text   A3     This will return TRUE if cell A3 contains Some Text

User · Answer

Check out the FIND   function in Excel    Syntax   FIND  substring  string   start position     Returns  VALUE  if it doesn t find the substring

User · Answer

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

User · Answer

This formula seems more intuitive to me    SUBSTITUTE A1  SomeText       lt  gt  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

[excel] Check whether a cell contains a substring

Examples related to excel

Examples related to excel-formula