How to check if a cell matches specific text in MS Excel

Some time ago, I was trying to retrieve a category field for records within a data sheet, however; there was no direct way to do so because the Category was embedded within the data that was in cells in a certain field. Consider the sample data below retrieved from a database.

My goal is to find out which of the three streams: i.e. “Accounts”, “Claims”, “Payments” each record belongs to. But there is no pre-existing field to indicate this and the only way to know which category each test case belongs to is by looking into the Path field to find those specific categories embedded within the each path.

In order to create a new field containing only the Categories (i.e. the highlighted values in the table above), we need to use three Excel functions:

• IF
• ISNUMBER
• SEARCH

Here is the formula I used to accomplish this:

=IF(ISNUMBER(SEARCH("Accounts",D4)),"Accounts", IF(ISNUMBER(SEARCH("Claims",D4)),"Claims",IF(ISNUMBER(SEARCH("Payments",D4)),
"Payments","NOT ASSIGNED")))

You may be a bit confused by seeing this, but don’t panic, I will explain it by breaking it down into smaller chunks:
First, The SEARCH function gives us the location of text in a string. For example, the SEARCH function tells us that the text word “Accounts” starts at the 17th character in cell D3. See screen shot below.

The ISNUMBER function checks if a value is a number. ISNUMBER will return TRUE when the value is numeric and FALSE when not. That said, if we embed the SEARCH function used earlier within the ISNUMBER function, a value of TRUE or FALSE will be returned.
​
In other words, if the Search function returns any number, this means that the respective category (I.e. “Accounts”, “Claims”, or “Payments”) exists in that cell and as a result, the ISNUMBER function will return a value of TRUE.  

Now that we know how the SEARCH and ISNUMBER functions operate, all we have to do is to place the formula from the screen shot above in a nested IF statement. Here is the syntax for a nested IF statement (i.e. for only three IF statements). 

IF(condition1, Value_if_True1, IF(condition2, Value_if_True2, IF(Condition3, Value_if_True3, “Value_If_None”)))

Here is how the breakdown works:

Condition1: is for the “Accounts” category, and if the condition is met “Accounts” will be displayed. Here is what must be entered for condition 1:             ISNUMBER(SEARCH("Accounts",D4))

Condition2: if for the “Claims” category, and if the condition is met “Claims” will be displayed. Here is what must be entered for condition 2:                           ISNUMBER(SEARCH("Claims",D4))

Condition3: if for the “Payments” category, and if the condition is met “Payments” will be displayed. Here is what must be entered for condition 3:           ISNUMBER(SEARCH("Payments",D4))

And if none of the conditions above is met the value “Not Assigned” will be returned, as indicated in the final formula:

​=IF(ISNUMBER(SEARCH("Accounts",D4)),"Accounts", IF(ISNUMBER(SEARCH("Claims",D4)),
"Claims",IF(ISNUMBER(SEARCH("Payments",D4)),"Payments","NOT ASSIGNED")))

 Let’s enter the formula in the spreadsheet and see what we get. As you could see in the screen shot below, the formula correctly returns “Accounts” for the first record.

And here is the final desired outcome once the formula is applied to the full range:

AWESOME!!!