Lesson#243: SEARCH and FIND Functions in Excel: Unleashing Text Search Power

In the vast realm of Excel, finding specific text within cells is a common and crucial task. This is where the SEARCH and FIND functions step in as your trusty text detectives, helping you locate and manipulate text data with precision. In this expert-level guide, we’ll delve deep into the capabilities of the SEARCH and FIND functions, using the keyword “SEARCH and FIND Functions” to underscore their importance. Moreover, we’ll provide you with over 20 practical examples to demonstrate how these functions can be your go-to tools for unleashing the power of text search and manipulation in Excel.

Understanding the SEARCH and FIND Functions

Before we embark on our text search journey, let’s establish a solid understanding of the syntax for both functions:

SEARCH Function:

=SEARCH(find_text, within_text, [start_num])

• find_text: The text you want to find.
• within_text: The text where you want to search.
• [start_num] (optional): The character position in within_text where the search should start.

FIND Function:

=FIND(find_text, within_text, [start_num])

The FIND function shares the same syntax as SEARCH and serves the same purpose: to locate text within other text. However, there is a crucial difference. While SEARCH is case-insensitive, FIND is case-sensitive.

Both functions return the position of find_text within within_text. If the text isn’t found, they return an error value (#VALUE!).

Now, let’s embark on our text search expedition with a plethora of practical examples.

Examples of Text Search with the SEARCH and FIND Functions

Example 1: Basic Text Search

Using SEARCH:

=SEARCH("Excel", "Explore Excel functions with SEARCH and FIND functions")

The SEARCH function identifies the position of “Excel” within the text and returns 9, indicating that “Excel” starts at the ninth character.

Using FIND:

=FIND("Excel", "Explore Excel functions with SEARCH and FIND functions")

The FIND function is case-sensitive and returns the same result, 9.

Example 2: Case-Insensitive Search

Using SEARCH:

=SEARCH("excel", "Explore Excel functions with SEARCH and FIND functions")

SEARCH is case-insensitive, so it finds “excel” even if it’s lowercase. It returns 9.

Using FIND:

=FIND("excel", "Explore Excel functions with SEARCH and FIND functions")

FIND is case-sensitive and doesn’t find “excel” in lowercase. It returns an error value (#VALUE!).

Example 3: Specifying the Starting Position

Using SEARCH:

=SEARCH("function", "Explore Excel functions with SEARCH and FIND functions", 15)

SEARCH starts searching from the 15th character and finds “function” at position 20.

Using FIND:

=FIND("function", "Explore Excel functions with SEARCH and FIND functions", 15)

FIND also starts at the 15th character and finds “function” at position 20, just like SEARCH.

Example 4: Handling Nonexistent Text

Using SEARCH:

=SEARCH("PowerQuery", "No mention of Power Query here")

SEARCH returns an error value (#VALUE!) because “PowerQuery” isn’t found.

Using FIND:

=FIND("PowerQuery", "No mention of Power Query here")

FIND also returns an error value (#VALUE!) for the same reason.

Example 5: Searching in Formulas

Using SEARCH:

=SEARCH("SUM", "=SUM(A1:A10)")

SEARCH can be used within formulas to identify text, making it useful for conditional logic based on text presence.

Using FIND:

=FIND("SUM", "=SUM(A1:A10)")

FIND serves the same purpose in formula-based text searches.

Example 6: Extracting Substrings

Using SEARCH:

=MID("Text extraction using SEARCH", SEARCH("extraction", "Text extraction using SEARCH"), 10)

SEARCH helps locate “extraction,” and MID extracts the subsequent 10 characters, giving you “extraction.”

Using FIND:

=MID("Text extraction using SEARCH", FIND("extraction", "Text extraction using SEARCH"), 10)

FIND works similarly with MID to extract the desired text.

Example 7: Handling Spaces

Using SEARCH:

=SEARCH("spaces", "Count  spaces    carefully")

SEARCH can find text regardless of extra spaces, returning 6 for “spaces.”

Using FIND:

=FIND("spaces", "Count  spaces    carefully")

FIND functions similarly in this context, returning 6.

Example 8: Finding the First Space

Using SEARCH:

=SEARCH(" ", "Find the first space")

SEARCH identifies the position of the first space and returns 4.

Using FIND:

=FIND(" ", "Find the first space")

FIND performs the same task, returning 4.

Example 9: Using Wildcards

Using SEARCH:

=SEARCH("r*s", "stars, bars, and cars")

SEARCH can utilize the asterisk (*) wildcard to find text patterns. It returns 1 for “stars.”

Using FIND:

=FIND("r?s", "stars, bars, and cars")

FIND, being case-sensitive, returns an error value (#VALUE!) for this wildcard search.

Example 10: Searching for Numbers

Using SEARCH:

=SEARCH("5", "Find all 5s in the text")

SEARCH can locate numbers within text. It returns 10 for the first occurrence of “5.”

Using FIND:

=FIND("5", "Find all 5s in the text")

FIND also identifies the position of the first “5,” returning 10.

Example 11: Handling Line Breaks

Using SEARCH:

=SEARCH(CHAR(10), "Text with" & CHAR(10) & "line breaks")

SEARCH can identify line breaks represented by CHAR(10).

Using FIND:

=FIND(CHAR(10), "Text with" & CHAR(10) & "line breaks")

FIND functions similarly in detecting line breaks.

Example 12: Handling Complex Text

Using SEARCH:

=SEARCH("21st century", "Explore the advancements of the 21st century")

SEARCH can locate complex text patterns, returning 30 for “21st century.”

Using FIND:

=FIND("21st century", "Explore the advancements of the 21st century")

FIND performs the same task, returning 30.

Example 13: Using SEARCH in Conditional Formatting

=SEARCH("highlight", A1)>0

SEARCH is valuable in conditional formatting rules, allowing you to apply formatting based on the presence of specific text.

Example 14: Using FIND for Password Validation

=IF(AND(ISNUMBER

(FIND("A", A1)), ISNUMBER(FIND("1", A1))), "Valid Password", "Invalid Password")

FIND can be used to validate passwords. This formula checks if “A” and “1” are present in a cell and returns “Valid Password” if both conditions are met.

Example 15: Extracting URLs

=MID(A1, FIND("https://", A1), FIND(".com", A1) + 4 - FIND("https://", A1))

FIND can be used to extract URLs from text. This formula locates “https://” and “.com” to extract the URL.

Example 16: Locating Email Addresses

=MID(A1, FIND("Email: ", A1) + 7, FIND("@", A1, FIND("Email: ", A1)) - FIND("Email: ", A1) - 7)

FIND helps locate email addresses within a cell and extract them using MID.

Example 17: Handling Currency Values

=MID(A1, FIND("$", A1), LEN(A1) - FIND("$", A1) + 1)

FIND can locate currency values denoted by “$” and extract them using MID.

Example 18: Extracting Phone Numbers

=MID(A1, FIND("(", A1), FIND(")", A1) - FIND("(", A1) + 1)

FIND can locate phone numbers within text using parentheses and extract them.

Example 19: Detecting Special Characters

=IF(ISNUMBER(SEARCH("~", A1)), "Special character found", "No special characters")

SEARCH helps identify the presence of the tilde “~” as a special character in a cell.

Example 20: Extracting File Extensions

=MID(A1, FIND(".", A1) + 1, LEN(A1) - FIND(".", A1))

FIND is used to locate file extensions within text and extract them.

Conclusion

The SEARCH and FIND functions are indispensable tools for locating and manipulating text within Excel. Whether you’re performing basic text searches, handling complex patterns, extracting specific data like URLs or email addresses, or validating input, these functions provide you with the precision and flexibility needed to work effectively with text data in your spreadsheets. By mastering SEARCH and FIND, you gain the ability to harness the full power of text search and manipulation, making Excel an even more powerful tool for your data analysis and management needs.

Stay updated with Excelabcd. Join our WhatsApp channel.

https://whatsapp.com/channel/0029Va5mZ9V1nozBRRpKbj38