How To Apply An If Statement If The Cell Only Contains A Partial Match To Text (With And Without VBA) -

Being able to apply an if statement, when there is a partial match of text, is useful. However, you may be wondering how this can be done quickly and easily either in Excel or in Excel’s programming language VBA.

In the case of VBA, the simplest way to create a partial match to text is to use the INSTR function which is relatively straightforward to use. Within Excel itself there are 3 different methods that can be used for completing a partial match with text within an if statement COUNTIF, FIND, or MATCH. I recommend that you use COUNTIF in all cases except those where there is a requirement for the function to be case-sensitive. In these cases, I recommend using FIND.

The MATCH function works in a similar manner to the COUNTIF function except that there is a requirement for an additional argument to be entered into the function. Additionally, the MATCH function produces an error if the text is not there.

The error produced within the MATCH function can be overcome by using an ISERROR function however the final if statement is more complex and generally more confusing because you will end up producing a false result when the text is present. An example of this is shown later on in the article.

Creating A Partial Match in VBA

To create an IF statement with a partial match in VBA it is best to use the in-string function (Instr) which has the following structure

InStr(string1, string2)

Please note that I’ve only included the required arguments within the function which will be sufficient to create your partial match.

String1 is the text that is being searched.

String2 is the text that has been searched for.

The in-string function will return the position that the first character appears in the text for example;

String1 = “seattle”, String2 = “tt”

Msgbox InStr(“seattle”, “tt”) returns the value 4 which represents the position of the first character that is being searched for.

However, it is important to note that the function is cases sensitive. To remove the case sensitivity add either LCASE or UCASE which will ensure that the case is identical.

MsgBox InStr(UCase(“seattle”), UCase(“T”))

If the text is not present in the string the value returned will be 0. To put this function into an if statement the code would be as follows;

If InStr(UCase(“seattle”), UCase(“T”))>0 then

‘If true execute the code

Else

‘If true execute the code

End If

Creating A Partial Match in Excel

Method 1 – COUNTIF

The first method that can be used is the COUNTIF function. This function is case insensitive but requires the use of a wildcard (*) on either side of the text that is being searched for.

To create a suitable if statement with count if the following formula would be required;

=IF(COUNTIF(A1, “*florida*”)=1, “florida is in the cell”, “florida is not in the cell”)

Method 2 – FIND

The second method is the function FIND has the following structure;

=FIND(find_text, within_text, [start_num])

where ;

Find_text is the text to be found

Within_text is the cell where the text is

[start_num] is an optional argument that allows the search text to start at a specific character in the text

Find will return the character position of the text if the text is present. If the text is not present an error will be produced.

Example

The formula =FIND(“Florida”, A1) was used to find if “Florida” was present in the cell

To successfully return a true or false value with FIND where the position of the text you are looking for is variable requires the use of an is error statement in conjunction with the find function to produce a true or false response. An example of how this statement would need to be produced for the example above is provided below;

=IF(ISERROR(FIND(“Florida”, A1)), “Florida is not in the cell”, “Florida is in the cell”)

Method 3 – MATCH

Using the MATCH function is another way to create a partial match. It is very similar to the FIND function but it has a disadvantage that an additional argument needs to be entered into the function to get a match and that match produces an error when the text is not present.