How do you find out whether a cell contains a certain string ?

- In VBA, it's easy.. use the INSTR function, and test whether this is greater than 0.

In Excel, you need to use both the LEN and the SUBSTITUTE functions.

=LEN(SUBSTITUTE(StringCell,StringToLookForCell,"",1))<>LEN(StringToLookForCell)

Let's see how this works.

- In plain English, all this is saying is:

• If I replace a string in another string with nothing.. is the length of the new string the same.
• If YES, then nothing was replaced, and the first string is not within the second.
• If NO, then something was replaced, and the first string IS within the second.

Here's our data:

Download sheet to practise finding out whether a cell contains a string

Training Video on whether cell contains a string: