✨ Part 1: Introduce
💡 Definition: The SEARCH function in Microsoft Excel is a text function used to find the position of a specific character or text string within another text string. It is similar to the FIND function but is not case-sensitive.
🎯 Purpose: The purpose of the SEARCH function is to locate the position of a specified character or text string within a larger text string. It allows users to search for a substring without considering the case, which can be helpful in various data analysis and manipulation tasks.
📚 Syntax & Arguments: The syntax of the SEARCH function is as follows:
=SEARCH(find_text, within_text, [start_num]) find_text: This is the required argument that specifies the text or character you want to find within another text.within_text: This is the required argument that represents the text string or cell reference within which you want to search for thefind_text.start_num: This optional argument denotes the positionwithin_textwhere the search should begin. If omitted, the search starts from the first character.
🔍 Explain the Arguments in the function:
find_text: It can be a text string or a cell reference containing the text you want to locate within thewithin_text.within_text: It is a text string or a cell reference containing the larger text string in which you want to search for thefind_text.start_num: It is an optional argument that allows you to specify the positionwithin_textwhere the search should start. If omitted, the search begins from the first character.
💡 Return Value: The SEARCH function returns the numeric value that represents the starting position find_text within the within_text. If the find_text is not found, it returns the #VALUE! Error.
📝 Remarks:
- The SEARCH function is not case-sensitive, meaning it treats uppercase and lowercase letters as the same.
- If you require a case-sensitive search, you can use the related function, FIND, instead.
- If the
find_textappears multiple times within, the SEARCH function returns the position of the first occurrence. - The SEARCH function counts each character, including spaces, as a position within the
within_text.
✨ Part 2: Examples
Let’s explore three examples that demonstrate the usage of the SEARCH function:
1️⃣ Example 1: Searching for a Character
| A | B | |
|---|---|---|
| 1 | Text | Position |
| 2 | Hello | =SEARCH(“l”, A2) |
| 3 | Excel | =SEARCH(“E”, A3) |
| 4 | World | =SEARCH(“d”, A4) |
In this example, we have text strings in column A, and we want to find the position of a specific character within each text string. The SEARCH function is used in column B to determine the status.
- The formula
=SEARCH("l", A2)cell B2 finds the letter “l” position within the text string “Hello”. It returns the value 3, indicating that the first “l” is at the third position. - The formula
=SEARCH("E", A3)cell B3 finds the position of the letter “E” within the text string “Excel”. It returns the value 1, indicating that “E” is at the first position. - The formula
=SEARCH("d", A4)in cell B4, find the position of the letter “d” within the text string “World”. It returns the value 5, indicating that “d” is at the fifth position.
2️⃣ Example 2: Extracting a Substring
| A | B | |
|---|---|---|
| 1 | Text | Substring |
| 2 | OpenAI | =MID(A2, SEARCH(“n”, A2), 4) |
| 3 | Microsoft | =MID(A3, SEARCH(“r”, A3), 6) |
| 4 | Python | =MID(A4, SEARCH(“t”, A4), 3) |
In this example, we have text strings in column A, and we want to extract a substring based on the position of a specific character. The SEARCH function is combined with the MID function in column B to remove the substrings.
- The formula
=MID(A2, SEARCH("n", A2), 4)in cell B2, find the letter “n” position within the text string “OpenAI” using the SEARCH function. Using the MID function, It extracts a substring of four characters starting from the “n” position. It returns the value “nAI”. - The formula
=MID(A3, SEARCH("r", A3), 6)in the cell, B3 finds the position of the letter “r” within the text string “Microsoft” using the SEARCH function. Using the MID function, It extracts a substring of six characters starting from the “r” position. It returns the value “soft”. - The formula
=MID(A4, SEARCH("t", A4), 3)in cell B4, find the position of the letter “t” within the text string “Python” using the SEARCH function. Using the MID function, It extracts a substring of three characters starting from the “t” position. It returns the value “tho”.
3️⃣ Example 3: Checking for Substring Existence
| A | B | |
|---|---|---|
| 1 | Text | Substring Found |
| 2 | OpenAI | =IF(ISNUMBER(SEARCH(“AI”, A2)), “Yes”, “No”) |
| 3 | Microsoft | =IF(ISNUMBER(SEARCH(“Corp”, A3)), “Yes”, “No”) |
| 4 | Python | =IF(ISNUMBER(SEARCH(“Java”, A4)), “Yes”, “No”) |
In this example, we have text strings in column A, and we want to check if a specific substring exists within each text string. The SEARCH function is combined with the IF and ISNUMBER functions in column B to determine the existence of the substring.
- The formula
=IF(ISNUMBER(SEARCH("AI", A2)), "Yes", "No")cell B2 checks if the substring “AI” is present within the text string “OpenAI” using the SEARCH function. If it exists, it returns “Yes”; otherwise, it replaces “No”. In this case, it replaces “Yes”. - The formula
=IF(ISNUMBER(SEARCH("Corp", A3)), "Yes", "No")cell B3 checks if the substring “Corp” is present within the text string “Microsoft”. It returns “No” because the substring is not found. - The formula
=IF(ISNUMBER(SEARCH("Java", A4)), "Yes", "No")cell B4 checks if the substring “Java” is present within the text string “Python”. It returns “No” as the substring is not found.
These examples showcase the versatility of the SEARCH function in locating specific characters or text strings within text strings and using the results for various operations and analyses in business scenarios.
4️⃣ Example 4: Filtering Data with Text Criteria
| A | B | C | |
|---|---|---|---|
| 1 | Product | Criteria | Filtered Data |
| 2 | Apple | =SEARCH(“e”, A2) | =IF(B2>0, A2, “”) |
| 3 | Banana | =SEARCH(“a”, A3) | =IF(B3>0, A3, “”) |
| 4 | Orange | =SEARCH(“o”, A4) | =IF(B4>0, A4, “”) |
In this example, we have a list of products in column A, and we want to filter the data based on specific criteria. The SEARCH function is nested with the IF function to determine if the requirements are present in each product, and the filtered data is displayed in column C.
- The formula
=SEARCH("e", A2)cell B2, search for the letter “e” in the product name “Apple” using the SEARCH function. If the letter “e” is found, it returns the position; otherwise, it returns an error. - The formula
=IF(B2>0, A2, "")in cell C2 checks if the position returned by the SEARCH function in cell B2 is greater than 0. If it is, the criteria are present in the product name, so the original product name is displayed in cell C2; otherwise, it shows an empty string (“”). - The formulas in cells B3 and C3 perform the same filtering for the product “Banana”.
- Similarly, the formulas in cells B4 and C4 filter the product “Orange”.
5️⃣ Example 5: Calculating Total Sales
| A | B | |
|---|---|---|
| 1 | Product | Quantity Sold |
| 2 | Apple | 10 |
| 3 | Banana | 5 |
| 4 | Orange | 8 |
| 5 | Total Sales | =SUMPRODUCT(–(SEARCH(“e”, A2:A4)>0), B2:B4) |
In this example, we have a list of products in column A and the quantity sold in column B. We want to calculate the total product sales with “e” in their names. The SEARCH function is nested with the SUMPRODUCT function to determine total sales.
- The formula
=SUMPRODUCT(--(SEARCH("e", A2:A4)>0), B2:B4)in cell B5, search for the letter “e” in the range A2:A4 using the SEARCH function. It returns an array of TRUE or FALSE values indicating whether the letter “e” is found in each product name. The double negative (– operator) converts the TRUE/FALSE values to 1/0. The SUMPRODUCT function then multiplies each 1/0 value with the corresponding quantity sold in column B. It returns the sum of the resulting values, giving us the total product sales with the letter “e” in their names.
6️⃣ Example 6: Data Validation
| A | B | C | |
|---|---|---|---|
| 1 | Product | Price | Validation |
| 2 | Apple | $1.99 | =IF(ISNUMBER(SEARCH(“$”, B2)), “Valid”, “Invalid”) |
| 3 | Banana | €0.99 | =IF(ISNUMBER(SEARCH(“$”, B3)), “Valid”, “Invalid”) |
| 4 | Orange | £1.49 | =IF(ISNUMBER(SEARCH(“$”, B4)), “Valid”, “Invalid”) |
In this example, we have a list of products in column A and their prices in column B. We want to validate if the prices are in the desired currency format (using the dollar symbol $). The SEARCH function is nested with the ISNUMBER and IF functions to perform the validation.
- The formula
=IF(ISNUMBER(SEARCH("$", B2)), "Valid", "Invalid")cell C2 checks if the price in cell B2 contains the dollar symbol “$” using the SEARCH function. If the dollar symbol is found, it returns “Valid”; otherwise, it replaces “Invalid”. - The formula in cell C3 performs the same validation for the price in cell B3, which is in euros (€).
- Similarly, the formula in cell C4 validates the price in cell B4, which is in pounds (£).
7️⃣ Example 7: Analyzing Customer Feedback
| A | B | |
|---|---|---|
| 1 | Feedback | Keyword Found |
| 2 | This product is excellent! | =IF(SEARCH(“excellent”, A2), “Yes”, “No”) |
| 3 | I am satisfied with the service. | =IF(SEARCH(“satisfied”, A3), “Yes”, “No”) |
| 4 | The customer support was helpful. | =IF(SEARCH(“helpful”, A4), “Yes”, “No”) |
In this example, we have customer feedback in column A, and we want to determine if specific keywords are present in each input. The SEARCH function is nested with the IF function to identify if the keywords are found.
- The formula
=IF(SEARCH("excellent", A2), "Yes", "No")cell B2 checks if the word “excellent” is found in the feedback “This product is excellent!” using the SEARCH function. If the word is found, it returns “Yes”; otherwise, it replaces “No”. - The formula in cell B3 checks if the word “satisfied” is found in the feedback “I am satisfied with the service.”
- Similarly, the formula in cell B4 checks if the word “helpful” is found in the feedback “The customer support was helpful.”
These examples demonstrate how the SEARCH function can be nested with processes commonly used in business scenarios to perform various data filtering, calculations, validations, and analysis tasks.
✨ Part 3: Tips and Tricks
Here are some tips and tricks related to the SEARCH function:
- Use the SEARCH function to search for individual characters and complete text strings within larger text strings.
- Combine the SEARCH function with other functions like MID, LEFT, or RIGHT to extract or manipulate specific parts of a text string based on the identified position.
- Use the related function FIND instead of SEARCH to perform a case-insensitive search.
- Remember that the SEARCH function returns an error (#VALUE!) if the specified text is not found. You can use the ISNUMBER function or error-handling techniques to handle such scenarios.
- Pay attention to the position returned by the SEARCH function, as it represents the starting position of the found text within the larger text string.
By utilizing the SEARCH function effectively, you can locate and extract specific information within text strings, enabling you to perform various data manipulations and analyses in Excel.