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.

score 412 · Accepted Answer · edited Aug 30 '16 at 15:36

412

Try using this:

=ISNUMBER(SEARCH("Some Text", A3))

This will return TRUE if cell A3 contains Some Text.

edited Aug 30 '16 at 15:36

broc.seib

21,643
8
63
62

answered Sep 04 '13 at 14:57

gwin003

7,432
5
38
59

8

Cunning! Thanks gwin003 :) I'm still a bit surprised there's not a more intuitive function for this. – geotheory Sep 04 '13 at 15:05
20

Yeah I agree, it would be nice if there were a `CONTAINS("Text", cell)` function. – gwin003 Sep 04 '13 at 15:25
2

Something I got stuck on at first: you really need to use `ISNUMBER` as well as `SEARCH`. I was using this inside an `IF` and came up with: `=IF(ISNUMBER(SEARCH($G$1,A73)), A73, B73)` – Matt Apr 27 '14 at 17:37
I guess the above also does the same, only addition is that if true out A73 value in the cell, if false, B73 value – peevesy May 27 '14 at 07:22
21

might be worth it to note that this i case insensitive, and if you want to match case, you should use `FIND()` in place of `SEARCH()` – Code Jockey Sep 11 '14 at 15:19
7

got an error using `,` instead of `;`. After changing the provided formula to `=ISNUMBER(SEARCH("Some Text"; A3))` it worked. Thanks! – renatov Apr 22 '15 at 19:18
7

@renatov that actually depends on the _locale_ of your OS; specifically, the character used for "list separator". – pepoluan Jul 09 '15 at 07:38
2

It's way too long of code! IF(OR(ISNUMBER(SEARCH(...),ISNUMBER(SEARCH(...))) THAT'S CRAZY TO READ!!! It should be: If( C4.Contains("something") OR C4.Contains("somethingelse")) so it can be readable and maintainable. – Skystrider Oct 20 '17 at 15:39
Excel has been around for so long and it still doesn't have a good expression builder. I recently started doing Telerik reports and I'm actually decently happy with building expressions using the expression builder. Can compare things by choosing from a drop down menu of options such as "=", "<>", "Contains", "Starts With". So much easier. If it's gotta be code then lets use normal code like JS. Otherwise use an expression builder that is easy to use. – Skystrider Oct 20 '17 at 15:42
If you want to use the result (string position) in a subsequent operation (MID), suggest IFERROR, e.g. `IFERROR(SEARCH("Some Text", A3), 0)`. – Nigel Touch Jun 21 '18 at 17:20
Just to be sure you not actually matching a substring within another substring (yes that's a thing) you could use `=ISNUMBER(FIND(" Some Text ", " "&A3" "))` Notice the spaces. – JvdV Jun 18 '20 at 09:53

score 27 · Answer 2 · edited Jan 29 '15 at 12:35

27

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")

edited Jan 29 '15 at 12:35

unexpectedvalue

6,079
3
38
62

answered Jan 11 '15 at 02:00

Steve

271
3
2

You can use 4 spaces or tab at beginning of the line to highlight the code block. – NiematojakTomasz Jan 11 '15 at 02:23

score 17 · Answer 3 · edited Dec 01 '17 at 23:36

17

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]

edited Dec 01 '17 at 23:36

Michel de Ruiter

7,131
5
49
74

answered Feb 08 '16 at 22:16

dsm

171
1
3

It works, but use of COUNTIF formula when huge data, makes file not responding, even file size becomes huge – Gaurravs Jun 14 '18 at 05:42

score 14 · Answer 4 · answered Mar 07 '16 at 14:39

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.

score 9 · Answer 5 · edited Jan 07 '15 at 11:50

9

Check out the FIND() function in Excel.

Syntax:

FIND( substring, string, [start_position])

Returns #VALUE! if it doesn't find the substring.

edited Jan 07 '15 at 11:50

Mathias Müller

22,203
13
58
75

answered Dec 20 '14 at 08:53

paras_doshi

1,027
1
12
19

1

Yes nested in `ISNUMBER` this also works, for case-sensitive matches only. – geotheory Mar 07 '16 at 17:39

score 4 · Answer 6 · answered Jun 17 '20 at 07:20

4

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

answered Jun 17 '20 at 07:20

Bjorn

51
2

1

Good way to improve personal efficiency, but at the cost of reproducibility I'd say :) – geotheory Jun 17 '20 at 11:19

score 3 · Answer 7 · answered May 13 '21 at 21:06

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 "**".

score 2 · Answer 8 · edited Apr 30 '16 at 13:48

2

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")

edited Apr 30 '16 at 13:48

Jakuje

24,773
12
69
75

answered Mar 23 '16 at 03:03

Givings

29
2

Welcome to SO. You should read up on what makes a [good answer](https://stackoverflow.com/help/how-to-answer). – geotheory Mar 23 '16 at 13:41
I don't understand what this is meant to do.. it checks for builder twice when presumably once would suffice. If it finds builder it returns builder, else it returns community. The word construction doesn't appear anywhere. Perhaps something like `=OR(COUNTIF(A1,"*builder*"),COUNTIF(A1,"*construction*"))`? – fantabolous Nov 19 '16 at 03:04
There is `COUNTIFS`: `COUNTIFS(A1,"*builder*",A1,"*construction*")` – vstepaniuk Jun 13 '20 at 15:11

score 2 · Answer 9 · answered Nov 14 '17 at 18:24

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.

most will want if/else ability, in other words a default value. Adding this quote might broaden the appeal of your answer: "There is no way to set a default if all tests return FALSE (i.e. a value if false). Instead, enter TRUE for the last test, and then a value to return as a default value if FALSE" — whitneyland, Feb 18 '19 at 05:25
IFS was added in Excel 2019, it is not present in Excel 2016. — robartsd, Dec 14 '22 at 23:11

Can.U · Answer 10 · 2021-03-17T23:47:31.960

0

Interesting *

=COUNT(MATCH("*SomeText*",A1,))
=COUNTA(VLOOKUP("*SomeText*",A1,1,))
=COUNTA(HLOOKUP("*SomeText*",A1,1,))

this returns 1 if "SomeText" is contained within A1.

edited Mar 17 '21 at 23:47

answered Mar 17 '21 at 06:09

Can.U

461
2
8

You could add `>0` on the end to return a boolean. – BigBen Mar 17 '21 at 23:55

score -3 · Answer 11 · edited Oct 15 '15 at 14:38

-3

Here is the formula I'm using

=IF( ISNUMBER(FIND(".",A1)), LEN(A1) - FIND(".",A1), 0 )

edited Oct 15 '15 at 14:38

Reeno

5,720
11
37
50

answered Oct 15 '15 at 14:14

Jim D

1

This checks to see if "." is included in A1, and if it is, it returns... the number of characters remaining in A1, starting with ".". Not sure whether that additional calculation is relevant here. – Grade 'Eh' Bacon Oct 15 '15 at 14:20
Leastwise, use `IFERROR(LEN(A1) - FIND(".", A1), 0)` – Nigel Touch Jun 21 '18 at 17:17

Check whether a cell contains a substring

11 Answers11

Linked

Related