How to Use the SEARCH, SEARCHBs Function in Excel

In spreadsheet applications such as Microsoft Excel and Google Sheets, the SEARCH function is invaluable when it comes to locating specific characters or substrings within a larger text string. This function, designed to find the position of a specified substring, operates independently of case, meaning it does not differentiate between uppercase and lowercase characters.

Basic Syntax

The basic syntax for the SEARCH function is as follows:

=SEARCH(find_text, within_text, [start_num])

• find_text: The text you are looking to locate.
• within_text: The body of text within which you are searching for find_text.
• start_num (optional): The character position to begin the search. If this parameter is omitted, the search starts at the first character.

Examples of Usage

Here are some practical examples to demonstrate how the SEARCH function is used in Excel and Google Sheets.

Finding a Substring

Consider a scenario where cell A1 contains the text string “apple orange banana”, and we need to locate the position of the word “orange”. The formula to achieve this would be:

=SEARCH("orange", A1)

This function will return 7, indicating that “orange” begins at the 7th character of the text string.

Case-Insensitive Search

One of the advantages of the SEARCH function is its case insensitivity. For instance, if we adjust the previous formula to search for “OrAnGe” instead, the function configuration would look like this:

=SEARCH("OrAnGe", A1)

The function still returns 7, emphasizing that it does not consider letter case in its search.

Specifying the Start Position

To tailor where your search begins within the string, you can specify the starting position. For example, to find the second occurrence of the letter “a” in “apple orange banana”, you might use:

=SEARCH("a", A1, SEARCH("a", A1) + 1)

This formula first determines the position of the initial “a” and then begins the subsequent search right after that point to find the next occurrence.

In summary, the SEARCH function is a powerful and flexible tool for locating substrings within larger text entries in Excel and Google Sheets. Familiarity with its syntax and capabilities allows users to effectively navigate and manage the data within their spreadsheets.

More information: https://support.microsoft.com/en-us/office/search-searchb-functions-9ab04538-0e55-4719-a72e-b6f54513b495