Update: Based on questions I’ve received, I added the Misc Notes section to the end of this post.
Continuing with our #FunctionFriday series, today we’re going to explore how to use the IF, SEARCH, and ISNUMBER functions together to find text (aka a string) inside other text for classification purpose. What Excel really needs is a CONTAINS function, so we don’t have to do these mental acrobats. But, for now, this is what we have to work with.
To illustrate it, I’ll use an example from a chart I include in client dashboards** where they use the AddThis or ShareThis WordPress plugin. The data from these plugins populates to your Google Analytics account. BUT the data is such a red-hot mess, you have to do some cleanup to get it all into nice, neat buckets.
If you’d like to include this report in your own reports, you can use this custom report I created. (If you get a 404 error, it’s because you’re not logged in to Google Analytics. You have to be logged in to apply the report to your Google Analytics profile, aka view.)
**If you want to become a beast at building dashboards using the Google Analytics API, my online course will take you there.
Download Excel Workbook
If you’d like to follow along, you can download the Excel workbook I used in this demo.
The Functions du Jour
IF Function
The IF function is the Swiss Army knife for marketers, especially when you’re building out dynamic dashboards. With the IF function you start with a test — e.g., if A3=B2, if A3>=100, if A3=”organic”, etc. — and then you specify what you want to return if the condition is true and what you want to return if the value is false. But the fun doesn’t stop there; you can actually embed IF functions inside of IF functions. And that’s what we’re going to do in this tutorial.
It follows the following syntax:
IF(logical_test, [value_if_true], [value_if_false])
SEARCH Function
The SEARCH function is a tricky little bugger. It returns the position of whatever character you search for. And if you enter a string of characters, it will return the position of the first character in the matching string. So if you searched for cheese in the string string cheese, it would return the value of 8.
The SEARCH function follows the following syntax:
SEARCH( substring, string, [start_position] )
substring: Pure, unbridled geek speak that means whatever text you’re searching for, e.g., cheese.
string: Typically the cell this text string is in, though you could enter text as long as you flank it with quotation marks. (I almost always use a cell reference.)
start_position: This is optional. I usually only use it when I’m searching for forward slashes in URLs and want to start searching after the http(s)://. You can check out an example in this post that describes how to extract domains from URLs.
Now, if you put a string inside a SEARCH function, it will look for that string exactly. But you can also throw the asterisk wildcard in there for a really good time, which tells Excel to flag any string that even contains the text you’re searching for. So going back to our string cheese example, if I searched for cheese, it would return FALSE; if I searched for *cheese*, it would return TRUE.
ISNUMBER Function
The ISNUMBER function simply tells you if the cell you’re referencing is a value (or number). It’s Boolean, meaning it returns either a value of TRUE or FALSE.
It follows the following syntax:
ISNUMBER(value)
Pro Tip
When dealing with a more complicated IF function, like you’ll see below, a good strategy is to break it into separate lines in the formula bar. To add a line break on a PC, use Alt-Enter; on a Mac, use Control-Option-Enter. I break my IF functions into different lines if I have more than three nested IFs. Here’s what it looks like for the formula we’ll use:
If you fat finger something in your formula, it’s really easy to see using this strategy because the IF functions won’t line up. When I’m finished, I just collapse the formula bar back to size by clicking-and-dragging the bottom of it.
Here’s what that same formula would look like if I didn’t use line breaks:
Putting It All Together
Okay, so here’s how we’re going to use them in concert. The Google Analytics custom report we’re using in this demo uses two dimensions: Social Source and Social Action and one metric, Social Actions. It’s a flat report, which, betedubs, is perfect for pivot tables. (Again, you should have data in this report if you use the ShareThis or AddThis plugin on WordPress.) But you could use this same strategy with any marketing data that you have to bucket based on snippets of text inside of strings.
If the formula in cell E4 could talk in plain English (wouldn’t that be nice?), here’s what it would say: “Hey, Excel, check out cell B4. If you see the string facebook anywhere in there, ISNUMBER will return a value of TRUE because you’ll return a number telling me the position of where the string I’m searching for. I don’t actually care about where that string is, only that it returns a number. If it does (IOW, it returns a value of TRUE), return the string Facebook; if it’s false, look for the next sting (i.e., twitter) and return Twitter … Rinse and repeat until you’ve cycled through all of the criteria. And if you don’t find facebook, twitter, email, linkedin, google, stumbleupon, pinterest, or sharethis, just call it Misc.” (Obviously, switch out sharethis for addthis if you’re using that plugin.)
Misc Notes
Difference Between SEARCH and FIND
Someone asked me about using the FIND function on Facebook instead of SEARCH. I rarely use the FIND function because it’s not as flexible as SEARCH. For one, it’s case sensitive. I can only remember one time using it because I needed to differentiate between cases. A much bigger liability is you can’t use it for partial matches because it doesn’t support Excel’s wildcard characters (* and ?).
Extracting Text Instead Of Categorizing It
So what if you didn’t want to match exact or partial text for the purpose of categorizing it like I have in this post with the help of the IF and ISNUMBER functions? What if, instead, you wanted to extract it instead? Then you would use the SEARCH function with the LEFT, RIGHT, and/or MID functions. I demonstrate how to do that in this tutorial on pulling domain names from a list of URLs. (I use this technique in conjunction with pivot tables in just about all competitive analysis I do because it allows me to group backlinks by domain.)
Finally! This is what I was looking for, also thanks for the small tip about the column break. One question I have is if you want to search for multiple variations of one of those words. So for example, in the ‘sharethis’ example, if you were looking to find a match for ‘share this’ and ‘sharethis’, so if either of those come up, it’s a match. Is it possible to search for more than one variation/word?
Thanks in advance, especially for putting this together.
-Neko
Happy it helped! And yes, you can. Excel has two different wildcards you can use (with the SEARCH function, not FIND, which is one of the reasons it’s lame). The ? matches any one character (so share?this would match both sharethis and share this), and the * matches one or more characters. To save on resources, I use the ? whenever possible. SEARCH also isn’t case sensitive, so share?this would also match Share This.
Thank you for the quick response. I used that easy example, but I’m more curious about search for totally different words? Like if I wanted the same search to catch, ‘share this’, ‘add this’, and ‘some other social thing’ and instead of ‘share this’ it put in ‘Social’?
You have to be careful with your pattern matching. If you get too loose and free with it, you’ll get unexpected results. I just experiment and see what I get. If I get what I want, I’ll be a little more free with the * character. If you want to search multiple criteria you’ll have to use an array formula or something like the SUMPRODUCT function (to avoid using an array function). Or you could nest another IF function. Lots of options.
Dear Annie,
Nice to know that you are helping us to resolve Excel doubts.
I have a simple question but seems to be difficult for me which I need your help.
Here it goes :
My Excel Column B cells may have different type of data, which can be pure numeric numbers, for example 100 or 2 or 0 or 3 etc. It can be mixed characters with numbers, for example, abc100 or >3 or or 10< or 200xyz.
Now, I need to just extract the numeric numbers out only.
Appreciate your kind advice to resolve my doubts, if possible with an email reply.
Thanks in advance.
Best Regards,
Jeffrey
********************
Your question is hardly simple. If your numbers are always together, you can use the MID function in an array formula to extract the numbers. If they’re not together, you’ll need to use a macro. This post covers both: http://superuser.com/questions/649475/extract-numbers-from-cells-containing-mixed-alpha-numeric-strings.
Dear Annie,
Thank you very much.
Appreciate your help.
Best Regards.
Jeffrey
*******************
No problem!
Annie, great article as always, my question: I’m scraping list data from a popular website into excel using SEO Tools. /li[1] to column C, /li[2] to column D, /li[3] to column E; the problem is the column headers, it’s usually C = UPC, D = Format, E = Rating but if Format is missing, now it’s C = UPC, D = Rating, E = Run Time…. Over a dozen items, it’s a mess. I’ve tried Find, Search, lookup, hlookup, Match, all looking for a way that I could have a separate column, search the scraped columns on that row for a partial match (ie. it returns “Format: Blu-ray” so I try to match “Format”, so that the headers match the data in the columns.
Any guidance is much appreciated.
I’m sorry … I can’t follow the issue. You lost me on: “… but if Format is missing, now it’s C = UPC, D = Rating, E = Run Time…. Over a dozen items, it’s a mess.”
Hi Annie,
Is there anyway to combine the search function with indirect? I have to look for about 400 different words in 20.000 cells so would like to use indirect to avoid the manual labour of having to type 400 different functions. Many thanks
Yes, you absolutely can. There are very few functions that can’t be combined. I can’t picture what you’re trying to do to help more, but I’d just experiment with it.
Hi Annie,
Thanks for the great function.
If you have two similar strings you are searching for (i.e. you want to find cells that contain both “AB” and “ABC” but do not want the cells with “ABC” to come up in the column searching for “AB”), have any suggestions on how I can differentiate them? Thanks!
I’ve read your question several times but don’t understand the distinction.
Very usefull, thanks alot.
Your example file made my day 😉
Excellent!
Extremely useful. Although it made sense when I read this – it took me a while to duplicate this to do what I needed, but I have got it now by simply going through the steps one at a time. Much apprecaited.
Always happy to hear!
Thank you! Very useful information and very well explained! Thank you for your time in sharing and teaching!!
My pleasure!
Brilliant, this technique made my “cleaning”-macro a whole lot faster 🙂
Excellent! Happy to help. 🙂
PUTTING NESTED IF STRINGS HAS LITERALLY CHANGED MY LIFE RIGHT NOW!
I don’t usually shout, but this is big news.
thank you
Best comment I’ve gotten in a while! ? Happy to help!
Thanks for the help – I am categorizing verbatim feedback from a survey, and this helps greatly!
You are amazing! This information is SOOOOOO helpful and easy to follow! THANK YOU!!!
This made my day! Happy to help!
Hi Annie
I got the word “CUF” in a column but in deferent text sentences. My column sample consist of 70000 cells.
I need to Identify the work in a empty column next to it as “CUF” only. Can you help me?
Can you share a Google Sheet with some example data? I can’t picture what you’re trying to do and what you need.
This post was *so* helpful! Thank you so much for sharing your expertise.
My pleasure, Heather!
Hi Annie,
Big fan of your site in Taiwan 🙂 and thanks for this tip. It works perfectly well!
What if, based on your example, there are more than 1000 social networks that I need to compare and extract? If I used the way you presented here it will make the formula super long and hard to manage. Is there any other smart way to do it?
You should really be querying a database with SQL for tasks like this. The next best thing would be advanced filters in Excel. I wrote this tutorial on Search Engine Land: https://searchengineland.com/advanced-filters-excels-amazing-alternative-to-regex-143680. And I did this video tutorial on how to use them: https://annielytics.com/blog/excel-tips/real-world-example-of-advanced-filters-in-excel-video/.
And thank you for the kind words! 🙂
Hi Annie,
Thanks for the blog! I have a question though, I am using the formula you have set out above, but what if the cell you are getting the information from has more than one of the IF’s. Can you add something to the formula so that you can display more than one result?
Thanks
Unfortunately, no. Excel will return the first match.
Found your site by internet search as I was look for a way to search for text in cells due to high freight bills posted in our SAP system. This worked like a charm.
Thanks!
Glad it helped, Jeff!
Dear Annie, this search function is very helpfull as it works. I only have the following problem. I would need to lookup 3,000 names/words. If I create the function in a cell than I receive the error message that only 64 strings can be entered. Is there a solution for this?
Many thanks for your help.
Regards Martijn
If you can use Google Sheets its regex functions are more effective. I use REGEXMATCH exclusively now.