How to Use the Search Function in Excel
In this Excel tutorial I show how to use the Search function in Excel. It’s a tool for finding text within other text, and while it might seem a little intimidating at first, it’s actually quite straightforward once you get the hang of it. Think of it as a more powerful version of Ctrl+F. While that shortcut helps you find things visually, Search lets you use those findings in formulas, opening up a whole new level of possibilities.
Understanding the Basics
The basic structure, or syntax, of the Search function looks like this: =SEARCH(find_text, within_text, [start_num]).
- find_text is simply the text you’re searching for. It could be a single letter, a word, or even a whole phrase. The key thing to remember is that Search isn’t case-sensitive. So, if you’re looking for “apple”, it’ll find “Apple”, “APPLE” and even “aPpLe.”
- within_text is where you’re doing the searching – the larger chunk of text you’re examining. And then there’s the optional
- start_num. This lets you specify where in within_text you want to begin the search. If you leave it out, Excel starts at the very beginning. This is handy if you’re looking for multiple instances of the same word.
How Search Works
So, how does it work? Search tells you the starting position of the text you’re looking for. Imagine you’re searching for “cat” in the sentence “The cat sat on the mat.” Search would tell you that “cat” starts at the 5th character. If it can’t find the text, it throws back a #VALUE! error. That’s important to keep in mind.
Combining with Other Functions: The Real Power
Now, just using Search by itself is okay, but its real strength comes from combining it with other Excel functions.
- For example, you can use it with Isnumber. Isnumber checks if something is a number. By combining it with Search, you can find out if a text string contains another string. You could write a formula like =ISNUMBER(SEARCH(“apple”, “I have an apple”)) and it would return TRUE. If you searched for “cat” instead, it would return FALSE.
- Another common pairing is with If. This allows you to create conditional logic. You could say =IF(ISNUMBER(SEARCH(“apple”, A1)), “Apple found!”, “Apple not found”) to display a message depending on whether “apple” is in cell A1. And if you need to extract a piece of text, you can combine Searchwith Mid. Mid pulls out a certain number of characters from a string. So, =MID(A1, Search(“apple”, A1), 5) would extract “apple” from cell A1, assuming it’s there.
- There’s also Find, which is similar to Search, but it is case-sensitive. So, you can choose the function that works best for your specific situation.
Think about how you can use this in real-world scenarios. You could clean up data, check if cells contain specific keywords, extract information, or even filter data based on text strings. There are tons of possibilities.
You can use wildcards like ? and * in your search. ? matches any single character, and * matches any sequence of characters. So, Search(“ap?le”, “apple”) would find both “apple” and “aplle.” Also, remember that #VALUE! error. It’s easy to forget about it and have your formulas break. Use IFERROR to handle those cases. For example, =IFERROR(SEARCH(“cat”, A1), “Not Found”) will display “Not Found” if “cat” isn’t in cell A1.
