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.
236
votes
11 Answers
382
votes
25
votes
13
votes
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.
13
votes
8
votes
2
votes
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")
2
votes
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
0
votes
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.
0
votes
0
votes
Why not simply
COUNTIF(A1,"*xyz*")
This searches for any appearence of "xyz" in cell A1.
It returns "1" when found, and "0" when not found.
Attention, the search is not case sensitive, so any of xyz, XYZ, XyZ, and so on will be found. It finds this as substrings in the cell, so also for abcxYz you get a hit.
If you do not want to write your search string into the formula itself, you can use
COUNTIF(A1,"*" & B1 & "*")
and enter your search string into B1. - Attention, when B1 is empty, the formula will return "found" ("1") as the search string is then read as "**".
