Search Function in Excel searches for a sub-string in another string and returns the position (as a number) of the given sub-string inside another string. SEARCH function returns a #VALUE error if the sub-string not found in the other.
Syntax of Excel Search Function
Here is the syntax of Excel Search Function. Search Function takes 3 arguments: Find text, Within Text and the Start Number (Optional).
=Search (find_text, within_text, [start_num])
Example:
=SEARCH("How", "Hello How Are You?",1)Returns: 7
Arguments:
- find_text: Sub string to find in another string or cell.
- within_text: Actual string to Search for the find_text value.
- [start_num]: Starting position in the text to search for sub string. It is an optional parameter; the default value is 1.
Return values:
- Search function returns a number representing the location of find_text in the within-text.
- #VALUE will be returned if there is no match found.
- #VALUE will be returned if start_num value is less than 0 or greater than the length of the within_text.
You can use the IFERROR to deal with this # VALUE Error.
Important Notes:
- Search function is case insensitive.
- This supports wild cards in searches.
- #VALUE will be returned if there is no match found.
- #VALUE will be returned if start_num value is less than 0 or greater than the length of the within_text value.
Examples:
Here are the Examples of Search Function. We have Find_text in Column B, Within_text in Column A and the Search Function in Column C. You can see the Results of the Search Function in Column C. The function returns the Position of the the find text in the within text when found. It returns the #VALUE when it is not found the find_text.
Check if a given text exist in a Cell.
The following Search Function checks for a String ( in Cell B23) within Cell A23. Returns ‘Exist’ if Found and ‘Not Exist” if not found.
=IF(SEARCH(B23,A23,1)>0,”Exist”,”Not Exist”)
Download Example Excel File with Search Function Formula
Here is the Example file with Excel Search Function Examples. You can download the file and explore the Example Formulas in the Search Spreadsheet.
