The Excel SEARCH function is a powerful text function that allows you to find the position of a substring within a text string. This function is case-insensitive and supports the use of wildcards.
Syntax
| Function | Description |
|---|---|
| SEARCH(find_text, within_text, [start_num]) | Finds the position of a specified substring within a text string. |
find_text: The substring you want to find.
within_text: The text string in which to search.
start_num: Optional. The position within the text string to start the search. Defaults to 1 if omitted.
Example
| Text String | Substring | Result |
|---|---|---|
| Hello, World! | World | 8 |
This example demonstrates how SEARCH can locate the substring "World" within the text string "Hello, World!". It returns 8, the position where "World" starts.
Practice Exercise 1
Task: Use the SEARCH function to find the position of "apple" in the following table.
Practice Exercise 2
Task: Use the SEARCH function to identify the position of "@" in the following email addresses.
Solution for Exercise 1
| Text String | Formula | Result |
|---|---|---|
| apple pie | =SEARCH("apple", "apple pie") | 1 |
| banana apple | =SEARCH("apple", "banana apple") | 8 |
| caramel apple | =SEARCH("apple", "caramel apple") | 9 |
In each case, the SEARCH function identifies the position where "apple" appears in the text strings.
Solution for Exercise 2
| Email Address | Formula | Result |
|---|---|---|
| [email protected] | =SEARCH("@", "[email protected]") | 8 |
| [email protected] | =SEARCH("@", "[email protected]") | 5 |
| [email protected] | =SEARCH("@", "[email protected]") | 5 |
The SEARCH function locates the "@" symbol within each email address, returning the position in the string.
Conclusion
The Excel SEARCH function is a versatile tool for locating substrings within text, benefiting a wide range of text processing tasks. By mastering this function, users can greatly enhance their data manipulation capabilities in Excel.