How to use Excel SEARCH Function (Examples + Video Tutorial)

Excel SEARCH Function

When to Use the Excel SEARCH Function in Excel

The Excel SEARCH function comes in handy when you need to determine the starting position of a specific substring within a text string. This can be immensely useful for tasks such as data extraction, conditional formatting, and more. By providing the position of the sought substring, the Excel SEARCH function enables you to precisely manipulate your data.

What Excel SEARCH Function Returns

In essence, the Excel SEARCH function returns the position of the first character of the searched substring within the given text. If the substring is not found, the function returns an error value, making it an excellent tool for validation and error checking in your worksheets.

Syntax of Excel SEARCH Function

The syntax of the Excel SEARCH function is as follows:

=SEARCH(find_text, within_text, [start_num])

Input Arguments

Let’s break down the input arguments for the Excel SEARCH function:

👉 find_text: This is the substring you want to find within the within_text. It can be a direct text entry, a cell reference, or a formula that evaluates to text.

👉 within_text: This is the text string in which you want to search for the find_text.

👉 start_num (optional): This argument lets you specify the starting position for the search within the within_text. If omitted, the search starts from the beginning. It’s especially useful when you want to find multiple occurrences of a substring.

Extra Notes

👉 If the find_text is not found within the within_text, the SEARCH function returns the #VALUE! error.

👉 The SEARCH function is case-insensitive, meaning it treats uppercase and lowercase letters as equivalent.

👉 The start_num argument cannot be negative. If it is negative or zero, the function returns an error.

Examples of the Excel SEARCH Function

Let’s explore a few examples of using the SEARCH function in Excel:

Example 1: Basic Usage

Suppose cell A1 contains the text “Excel functions are powerful.” You want to find the position of the word “functions.” In cell B1, use the formula:

Example 2: Using the start_num Argument

Let’s say you want to find the second occurrence of “functions” in cell A1. In cell B1, use the formula:

Here’s what each part of the formula does:

(1) SEARCH(“functions”, A1)

• This inner SEARCH function searches for the first occurrence of the word “functions” within the text in cell A1. Let’s say this position is 7 (counting spaces and characters).

(2) SEARCH(“functions”, A1) + 1

• This part adds 1 to the position found in step 1. In our example, it would be 8. This adjustment is made so that the search for the second occurrence starts from the character immediately after the first occurrence.

(3) SEARCH(“functions”, A1, SEARCH(“functions”, A1) + 1)

• This outer SEARCH function starts searching for the word “functions” again, but this time, it starts from the position calculated in step 2 (8). This effectively skips the first occurrence and looks for the second occurrence.

Example 3: Extracting Text Between Brackets

Suppose you have a list of product codes in column A, and the product codes are enclosed in square brackets. Your goal is to extract the product codes from within the brackets.

Let’s break down the formula step by step:

(1) SEARCH(“[“, A1)

• This part of the formula searches for the position of the opening square bracket [ within the text in cell A1.

(2) SEARCH(“]”, A1):

• This part of the formula searches for the position of the closing square bracket ] within the text in cell A1.

(3) SEARCH(“]”, A1) – SEARCH(“[“, A1) – 1

• This calculates the length of the text between the opening and closing square brackets. It subtracts the position of the opening square bracket from the position of the closing square bracket and then subtracts 1 to exclude the brackets themselves.

(4) MID(A1, SEARCH(“[“, A1) + 1, SEARCH(“]”, A1) – SEARCH(“[“, A1) – 1)

• This part of the formula uses the MID function to extract the text between the brackets. The MID function takes three arguments: the text from which to extract (A1), the starting position (found in step 1), and the number of characters to extract (calculated in step 3).

Excel Search Function – Video Tutorial

Difference Between SEARCH Function & FIND Function

Both the SEARCH and FIND functions in Excel are used to locate the position of a specific substring within a text string. However, there is a crucial difference between the two functions: case-sensitivity.

• Use SEARCH when you want a case-insensitive search. For instance, if you’re searching for the word “mango”, SEARCH will find “mango”, “Mango” and “MANGO”.
• Use FIND when you want a case-sensitive search. For example, if you’re looking for the word “mango”, FIND will only find “mango” and not “Mango” or “MANGO”.

Conclusion

In conclusion, the Excel SEARCH function is an invaluable tool for locating substrings within text strings. Whether you are cleaning up data, validating entries, or performing advanced analysis, the SEARCH function empowers you with precise control over your text data.

Remember, the true mastery of Excel functions comes from hands-on practice. So, don’t hesitate to experiment and apply the SEARCH function in various scenarios. Start using the SEARCH function today to unlock new dimensions of efficiency and accuracy in your Excel projects!

Frequently Asked Questions (FAQs)

Q1: Can the SEARCH function be used for case-sensitive searches?

A1: No, the SEARCH function is case-insensitive and treats uppercase and lowercase letters as the same.

Q2: What does the SEARCH function return if the find_text is not found?

A2: If the find_text is not found, the SEARCH function returns the #VALUE! error.

Q3: Can the start_num be a negative value?

A3: No, the start_num must be a positive integer. Negative values will result in an error.

Q4: Can the SEARCH function be used for finding multiple occurrences of a substring?

A4: Yes, by adjusting the start_num argument, you can find multiple occurrences of a substring within the within_text.

Q5: Is there a way to perform case-sensitive searches in Excel?

A5: Yes, for case-sensitive searches, you can use the FIND function instead of SEARCH.

Other Related Excel Functions

LEN Function

UPPER Function

LOWER Function

Join me on Instagram and YouTube for your daily dose of valuable tips and tricks! Catch insightful videos that will enhance your knowledge and skills. Don’t miss out – follow me now!