Search Function in Excel

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:

Search Function in Excel - 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.

Search Function in Excel - Examples

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.

Search Function – Examples.xlsx

Search Function in Excel – Real-time Examples

Share This Story, Choose Your Platform!

Leave A Comment

Excelx.com is a Excel Blog by PNRao to provide latest tips of Excel and help you to Quickly Learn and up-to date with Excel Concepts. We help you to know – how to do things in Excel with Clean and Easy Excel Tips, Shortcuts, Reference, Tutorials and New and Quick Tricks and Methods.

We Provide Free Online Tutorials, Formulas, Templates, Dashboards and Macros to excel in Microsoft Excel. We cover basic to advanced concepts and share creative ideas, Excel tips and Shortcuts with Related Example Files. You Can refer verity of examples covered on latest tools and techniques in Microsoft Excel Applications.

Related Posts