WISDOMBYDATA
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me

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

8/7/2016

0 Comments

 
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.
Picture
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.
Picture
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.
Picture
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.  
Picture
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.
Picture
And here is the final desired outcome once the formula is applied to the full range:
Picture
AWESOME!!!
0 Comments

Your comment will be posted after it is approved.


Leave a Reply.

    Categories

    All
    BI
    EXCEL
    MISC
    SQL

    Archives

    June 2020
    May 2020
    April 2020
    March 2020
    February 2020
    December 2019
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    May 2015
    April 2015
    March 2015
    February 2015
    January 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    April 2014
    March 2014
    February 2014
    January 2014
    December 2013
    November 2013

Powered by Create your own unique website with customizable templates.
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me