Excel IF Function Contains Text – A Partial Match in a Cell

In this post, we will look at how to use the IF function to check if a cell contains specific text.

The IF function when used to compare text values, checks for an exact match. But in this blog post we want to check for a partial match. We are interested if the cell contains the text anywhere within it.

For our example, we have a list of addresses as shown below. And we want to display the word ‘local’ if the address contains the text ‘CB2’.

So any postcode beginning with ‘CB2’ is considered local. Anything else is not.

Watch the Video – IF Function Contains Text Match

Check If Cell Contains Text

As mentioned, the IF function always performs an exact match. Therefore we need a different function to determine if the text is in the cell or not.

The function we will use is SEARCH. This function will return the position of the text inside the cell, if it is present.

=SEARCH("CB2 ",A2)

You have to be careful when testing for partial matches. And in this example a space is added after CB2 in the string to search for. This is done to prevent it finding matches for postcodes such as CB25 or CB22.

There is also a function called FIND which could be used instead of SEARCH. The key difference between the two is that FIND is case sensitive, and SEARCH is not.

Now this function returns the position of the text if found. We are not interested in this, and simply want to know if it exists.

So we will surround the SEARCH function with ISNUMBER to return TRUE if the text is found, and otherwise return FALSE.

 =ISNUMBER(SEARCH("CB2 ",A2))

We can now finally add the IF function to display the word ‘local’ if ISNUMBER reports TRUE, and the word ‘far’ if FALSE is returned.

=IF(ISNUMBER(SEARCH("CB2 ",A2)),"local","far")

How about Searching for Two Pieces of Text

Lets take it a step further. And what if we consider an address to be local if it contains the text ‘CB2’ or ‘CB3’.

We will bring in the OR function for this. A function that can test multiple conditions and returns TRUE if any one of its tests are met.

The formula below can be used. This time the cell is made blank if it is not local.

=IF(OR(ISNUMBER(SEARCH("CB2 ",A2)),ISNUMBER(SEARCH("CB3 ",A2))),"local","")