SEARCH Formula in Excel (Table of Contents)
SEARCH Formula in Excel
- Search Function is one of the most important in-built function of MS Excel. It used to locate or find one string in the second string; in simple words, it will locate a search text in the string. The Search function allows the wildcards (like: ??, *, ~), and it is not case-sensitive.
- For example, if there is a first-string “base” and a second string is the “database”, then the search function will return as 5, starting from the fifth letter in the second string.
Search Formula Syntax in Excel
SEARCH ()– It will return an integer value, which represents the position of the search string in the second string. There is three-parameter – find_text, within_text, [start_num].
The Argument in the SEARCH Function
- find_text: It is a mandatory parameter, the string which the user wants to SEARCH.
- within_text: It is a mandatory parameter, the string in which the user wants to SEARCH.
- find_text: It is an optional parameter, the character number from where the user wants to search in the within_text.
How to Use SEARCH Formula in Excel?
There is some statement given in the table and a search string also given which a user wants to search from the given statement. Let’s see how the SEARCH function can solve this problem.
Example #1 – Use SEARCH Formula in Text
Open the MS Excel, Go to Sheet1 where the user wants to SEARCH the text.
Create one column header for the SEARCH result to show the function result in the C column.
Click on the C2 cell and apply the SEARCH Formula.
Now it will ask for find text; select the Search Text to search, which is available in B2.
Now it will ask for within the Text, from where the user wants to search text which available in B2 cell.
It will now ask for start Num, which by default value is 1, so we will give 1>> write in C2 cell.
Press the Enter key.
Drag the same formula to the other cell of the C column to find out the SEARCH Result.
Summary of Example 1:
As the user wants to SEARCH text in the given statement, the same he can achieve by the search function. Which is available in column C as the search result.
Example #2 – Use the SEARCH Function for Wildcards
There is some data given that have wildcards (like: ??, *, ~) in the table and search string also given which a user wants to search from the given data.
Let’s see how the SEARCH function can solve this problem.
Open the MS Excel, Go to Sheet2 where the user wants to SEARCH the text.
Create one column header for the SEARCH result to show the function result in the C column.
Click on the C2 cell and apply the SEARCH Formula.
Now it will ask for find text; select the Search Text to search, which is available in B2.
It will now ask for within Text, from where the user wants to search text that is available in cell B2.
It will now ask for start Num, which is by default value is 1, so we will give 1 >> write in C2 cell.
Press on the Enter key.
Drag the same formula to the other cell of the C column to find out the SEARCH result.
Summary of Example 2:
As the user wants to SEARCH text in the given data, the same he can achieve by the search function. Which is available in the C column as the search result.
Example #3 – Use the SEARCH Function with the help of LEFT Function
There are some company employee details given in a table from where a user wants to get the first name of all employees.
Open the MS Excel; go to Sheet3, where the user wants to get the first name of all employees.
Create one column header for the SEARCH Result to show the function result in the B column.
Click on cell B2 and apply the first Left formula.
Now select cell B2, where the LEFT Formula will be applied.
Now apply the SEARCH Formula.
Now it will ask for find text; the user will search for the first gap.
Now it will ask for within Text, from where the user wants to employees the first name which available in A2 cell.
It will now ask for start Num, which is by default value is 1, so we will give 1 >> write in B2 cell.
Now it will ask for num_char, which is the result of search function >> here space also counts as one character so need to minus 1 from the search function result.
Press on the Enter key.
Drag the same formula to the other cell of the B column to find out the SEARCH Result.
Summary of Example 3:
As the user wants to find out the first name of the employee. Same he has achieved by left and search function, which is available in D column as the search result.
Things to Remember About SEARCH Formula in Excel
- The SEARCH function will return an integer value. A user can use this function with other formulas and functions.
- The Search function allows wildcards (like: ??, *, ~), and it is not case-sensitive. If a user wants to search for case-sensitive, then use the FIND function.
- If there is no matching for find_search in within_serach, then it will throw a #VALUE! Error.
- If start_num is greater than the length of the within_serach string or not greater than zero, then it will return a #VALUE! Error.
Recommended Articles
This has been a guide to SEARCH Formula in Excel. Here we discuss How to Use SEARCH Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
120+ Online Courses | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9
View Course
Related Courses
