Beginners Guide on How To Search in Excel
Jun 23, 2023
Are you looking for a function that allows you to search efficiently within an Excel spreadsheet?
The SEARCH function is an Excel feature that makes searching for specific text strings or values inside your worksheets easy. Indeed, it is one of the most important functions you should know.
This helpful function can save you time, effort, and energy when finding what you need from large data sets.
Today, we'll share a comprehensive guide on the SEARCH function in Excel.
Read on as we cover the following:
-
What Is the Excel SEARCH Function
-
SEARCH Function Examples
-
Related Excel SEARCH Function Examples
-
Final Thoughts on How to Search within Excel
-
Frequently Asked Questions on How to Search within Excel
Microsoft Excel SEARCH Function
The SEARCH function returns the position as a numeric value of one text string within a cell.
Moreover, you can use this feature to locate multiple instances of the search string while only returning the position of the first occurrence.
Furthermore, the SEARCH function in Excel supports a case-sensitive search. However, it doesn't support some wildcard characters. If the SEARCH function doesn't find a value, it will result in a "#VALUE!" error.
The SEARCH function syntax and arguments
The image below represents the SEARCH function syntax:
The SEARCH function arguments are the following:
-
Find_text - the substring or the character you want to find in your Excel spreadsheet.
-
Within_text - the text string or the cell reference, where you will find the character.
-
Start_num - the optional argument, the starting position of the character you find.
Note:
-
The SEARCH function lets you use wildcard characters like the question mark (?) and asterisk (*) in the "find_text" field.
-
The question mark identifies any single character, and the asterisk matches any sequence of characters.
-
To search for a literal? or , use a tilde (~) before the character, i.e. ~ and ~?.
SEARCH Function Example
To learn how to use the SEARCH function, follow the steps below:
-
Prepare your data.
-
Select a cell, then type the SEARCH formula.
-
Type the "find_text" value.
-
Type the within_text value. It can be a text value or a cell reference.
-
After completing the Excel formula, press the Enter key.
The SEARCH function returns a numeric value of 3, which means the find text argument is in the third letter of the text string.
Related Excel SEARCH Function Examples
These are related Excel SEARCH function examples which combine the feature with other Excel functions.
Checking the occurrence of a text string.
We can combine the ISNUMBER and SEARCH functions to check the occurrence of a substring.
The ISNUMBER function can help determine if a value is a number or text by checking the result of the SEARCH function.
Moreover, if the SEARCH function retrieves a number, the function ISNUMBER will return TRUE. On the other hand, if SEARCH returns a "#VALUE!" error, ISNUMBER will return FALSE.
Change a substring if it occurs.
We can use the SEARCH and REPLACE functions to exchange one if it occurs in the original text string.
The REPLACE function is a worksheet function that replaces a part from a text string based on a specified number with a different text string.
Withdraw a substring from a larger text string.
We can use the SEARCH function with the LEFT, RIGHT, and MID functions to extract specific characters from a text string.
Additionally, you can use these functions by searching for the character or beginning of the text string. Then, you can use that location to extract the desired substring.
LEFT function:
RIGHT function:
MID function:
Final Thoughts on How to Search within Excel
The ability to search within Microsoft Excel can be an incredible skill to help you make the most of your data.
It can save time and free up valuable resources for other important projects.
Visit Simple Sheets for more easy-to-follow guides and examples, and remember to read the related articles section of this blog post.
For the most straightforward Excel video tutorials, subscribe to Simple Sheets on YouTube!
Frequently Asked Questions on How to Search within Excel
Can I use wildcards with the SEARCH function in Excel?
No, the SEARCH function in Excel does not support wildcards. It performs a simple text search without considering wildcard characters like asterisks (*) or question marks (?).
Can the SEARCH function search for multiple text strings in Excel?
No, the SEARCH function in Excel can only search for a single text string at a time.
How can I differentiate between the SEARCH and FIND functions in Excel?
-
SEARCH is case-insensitive, meaning it does not distinguish between uppercase and lowercase letters.
-
FIND is case-sensitive, meaning it considers the case of the text when searching for a match.
Related Articles
Excel Pivot Table Training: Everything You Need to Know
Excel Definition: What is Excel, and What is its Purpose?
Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
