Applying FIND and SEARCH Functions in Excel

Course: Advanced Excel

Topic: Applying FIND and SEARCH Functions

Course Link: Advanced Excel Training – JustAcademy

Demo Link: Register for a Free Course Demo

Website: JustAcademy Official Website

1. Introduction to FIND and SEARCH Functions

Microsoft Excel provides several text functions for extracting, analyzing, and manipulating text data. The FIND and SEARCH functions help users locate the position of a specific character, word, or text string within another text string.

These functions return the numerical position at which the specified text begins. They are especially useful when working with customer records, product codes, email addresses, employee details, transaction descriptions, and imported datasets.

Although FIND and SEARCH perform similar tasks, their main difference is that FIND is case-sensitive, whereas SEARCH is case-insensitive and supports wildcard characters.

Learning Objectives

• Understand the purpose of FIND and SEARCH functions.
• Learn the syntax and arguments of both functions.
• Identify the differences between case-sensitive and case-insensitive searches.
• Use FIND and SEARCH with real-world datasets.
• Combine these functions with LEFT, RIGHT, MID, IFERROR, and other Excel functions.
• Troubleshoot common errors and build dynamic text-processing formulas.

2. Understanding the FIND Function

The FIND function locates the position of one text string inside another text string. It is case-sensitive, meaning uppercase and lowercase letters are treated differently.

Syntax

=FIND(find_text, within_text, [start_num])

Arguments Explained

Example 1: Finding a Character Position

Suppose cell A2 contains the text Advanced Excel Training.

Important: Excel counts spaces and punctuation as characters. The first character of a text string is position 1.

Example 2: Using the start_num Argument

Assume cell A2 contains banana.

=FIND("a",A2)

Result: 2, because the first lowercase a occurs at position 2.

=FIND("a",A2,3)

Result: 4, because Excel starts searching from position 3 and finds the next a at position 4.

The start_num argument is useful when the same character appears multiple times in a text string.

3. Understanding the SEARCH Function

The SEARCH function also locates the position of a text string within another string. Unlike FIND, it is not case-sensitive and supports wildcard characters.

Syntax

=SEARCH(find_text, within_text, [start_num])

Arguments Explained

Example 1: Searching Without Case Sensitivity

Suppose cell A2 contains Advanced EXCEL Training.

Example 2: Using Wildcards in SEARCH

SEARCH supports two important wildcard characters:

• Asterisk (*): Matches any sequence of characters, including no characters.
• Question mark (?): Matches exactly one character.

Suppose cell A2 contains Advanced Excel Training.

=SEARCH("Excel*",A2)

Result: 10. The formula locates Excel followed by any sequence of characters.

=SEARCH("E?cel",A2)

Result: 10. The question mark matches the character between E and cel, so the pattern matches Excel.

To search for an actual asterisk or question mark instead of using it as a wildcard, place a tilde (~) before the character, such as =SEARCH("~*",A2).

4. Difference Between FIND and SEARCH

Which Function Should You Use?

• Use FIND when uppercase and lowercase characters must be distinguished.
• Use SEARCH when capitalization may vary across records.
• Use SEARCH when wildcard patterns are required.
• Use IFERROR with either function when missing search terms should produce a custom result.

5. Practical Example: Searching Customer Email Addresses

Suppose an Excel worksheet contains customer email addresses. You want to identify the position of the @ symbol to help validate or process the data.

These formulas identify the position of the @ symbol. This can be useful for further text extraction, although locating @ alone does not confirm that an email address is valid.

6. Combining FIND with LEFT

The LEFT function extracts characters from the beginning of a text string. Combining LEFT with FIND allows you to extract text that appears before a specific character.

Example: Extracting a Username from an Email Address

Suppose B2 contains [email protected].

=LEFT(B2,FIND("@",B2)-1)

Result: rahul

How it works:

1. FIND locates the @ symbol at position 6.
2. Subtracting 1 gives 5 characters.
3. LEFT extracts the first five characters.

7. Combining SEARCH with MID

The MID function extracts text from the middle of a string. SEARCH can identify where the required text begins, allowing MID to extract a section dynamically.

Example: Extracting a Course Name from a Description

Suppose A2 contains Course: Excel Advanced Level, and you want to extract the word Excel.

=MID(A2,SEARCH("Excel",A2),5)

Result: Excel

SEARCH finds the starting position of Excel, while MID extracts five characters from that position.

8. Combining FIND and SEARCH with IFERROR

When the requested text does not exist, FIND and SEARCH return the #VALUE! error. IFERROR can replace that error with a meaningful message or alternative value.

Example 1: Displaying a Custom Message

=IFERROR(SEARCH("Excel",A2),"Text Not Found")

If Excel exists in A2, the formula returns its starting position. Otherwise, it displays Text Not Found.

Example 2: Checking Whether a Keyword Exists

=IF(ISNUMBER(SEARCH("urgent",A2)),"Urgent","Normal")

This formula checks whether the word urgent appears in the text, regardless of capitalization. If found, it returns Urgent; otherwise, it returns Normal.

9. Using FIND and SEARCH with IF

Combining text-search functions with IF allows you to categorize records based on keywords.

Example: Identifying Product Categories

Suppose A2 contains a product description, such as Wireless Bluetooth Headphones.

=IF(ISNUMBER(SEARCH("headphones",A2)),"Audio","Other")

Result: Audio

The formula searches for headphones without considering capitalization and assigns the Audio category when the keyword is present.

For several categories, nested IF functions or modern alternatives such as IFS can be used. When multiple keywords may appear in the same description, define a clear priority order to avoid unexpected classifications.

10. Advanced Applications of FIND and SEARCH

10.1 Extracting Text Before a Hyphen

Suppose A2 contains EMP-1058. To extract the prefix before the hyphen, use:

=LEFT(A2,FIND("-",A2)-1)

Result: EMP

10.2 Extracting Text After a Hyphen

To extract the text after the first hyphen, use:

=MID(A2,FIND("-",A2)+1,LEN(A2))

Result: 1058

10.3 Finding the Second Occurrence of a Character

Suppose A2 contains sales.region.india. To locate the second period, use:

=FIND(".",A2,FIND(".",A2)+1)

Result: 13. The inner FIND locates the first period, and the outer FIND begins searching immediately after it.

10.4 Identifying Text Containing a Keyword

To check whether a customer comment contains the word refund, regardless of case, use:

=IF(ISNUMBER(SEARCH("refund",A2)),"Refund Request","Other")

This can help categorize customer feedback, support tickets, and service requests.

10.5 Extracting Text Before a Space

To extract the first word from a text string in A2, use:

=LEFT(A2,FIND(" ",A2)-1)

This works when A2 contains at least one space. If the cell contains only one word, FIND returns an error. A safer formula is:

=IFERROR(LEFT(A2,FIND(" ",A2)-1),A2)

11. Common Errors and Troubleshooting

12. Best Practices

• Choose FIND or SEARCH based on whether capitalization matters.
• Use cell references instead of hard-coded text whenever possible.
• Combine text-search functions with LEFT, RIGHT, MID, and LEN for flexible extraction.
• Use IFERROR when missing text is an expected possibility.
• Test formulas with blank cells, missing delimiters, repeated characters, and different capitalization.
• Document complex formulas so other users can maintain the workbook.
• For newer Excel versions, consider TEXTBEFORE and TEXTAFTER when extracting text around delimiters is the main goal.
• Remember that FIND and SEARCH return character positions, not the number of occurrences or the matching text itself.

13. Hands-On Practice Exercises

Use the following sample data to practice FIND and SEARCH.

Suggested Solutions

1. =FIND("Advanced",A2) returns 7.
2. =FIND("@",A3) returns 9.
3. =LEFT(A4,FIND("-",A4)-1) returns EMP.
4. =IF(ISNUMBER(SEARCH("urgent",A5)),"Found","Not Found") returns Found.
5. =FIND(".",A6,FIND(".",A6)+1) returns 12.

14. Mini Project: Customer Feedback Classification

Objective: Build an Excel worksheet that classifies customer feedback according to keywords.

Sample Data

Project Steps

1. Enter customer IDs and feedback comments into an Excel worksheet.
2. Use SEARCH to locate keywords in each comment.
3. Combine SEARCH with ISNUMBER and IF to classify feedback.
4. Use IFERROR or appropriate logical checks to handle missing keywords.
5. Copy formulas down the dataset and verify the results.
6. Create a summary of the number of records in each category using COUNTIF.

Expected Outcome: A dynamic feedback-classification worksheet that demonstrates practical text analysis and formula-based automation.

15. Interview Questions and Answers

Q1. What is the purpose of the FIND function?

Answer: FIND returns the starting position of a specified text string within another string and is case-sensitive.

Q2. What is the difference between FIND and SEARCH?

Answer: FIND is case-sensitive and does not support wildcards. SEARCH is case-insensitive and supports wildcard characters.

Q3. What happens when the specified text is missing?

Answer: Both functions return a #VALUE! error when the specified text cannot be found.

Q4. How can you prevent errors when searching for text?

Answer: Use IFERROR to display a custom message or an alternative value when the search fails.

Q5. How can FIND help extract a username from an email address?

Answer: Combine FIND with LEFT. For example, =LEFT(A2,FIND("@",A2)-1) extracts the text before the @ symbol.

Q6. Can SEARCH locate text regardless of capitalization?

Answer: Yes. SEARCH is case-insensitive, so it can locate Excel, EXCEL, or excel using the same search term.

Q7. What is the purpose of the start_num argument?

Answer: It specifies the character position where the search begins, which is useful when a character appears multiple times.

Q8. Can FIND and SEARCH be used with other Excel functions?

Answer: Yes. They are commonly combined with LEFT, RIGHT, MID, LEN, IF, ISNUMBER, and IFERROR.

16. Quick Revision Checklist

• FIND locates text using case-sensitive matching.
• SEARCH locates text without case sensitivity.
• SEARCH supports the * and ? wildcard characters.
• Both functions return a numerical character position.
• The optional start_num argument controls where searching begins.
• Both functions return #VALUE! if the requested text is not found.
• IFERROR can help handle missing search terms.
• LEFT, MID, and RIGHT can use FIND or SEARCH to extract text dynamically.

17. Conclusion

The FIND and SEARCH functions are essential tools for text analysis in Advanced Excel. They allow users to locate characters, identify keywords, extract information, and classify text-based records. Understanding their differences helps you select the correct function for each task.

By combining FIND and SEARCH with other Excel functions, you can build flexible formulas for cleaning datasets, analyzing customer feedback, processing email addresses, and automating reporting workflows. Regular practice with real-world data will improve your formula-writing skills and confidence in advanced spreadsheet analysis.

Learn Advanced Excel with JustAcademy

Explore the course to develop your Excel skills, practice advanced formulas, and work on practical data-analysis tasks.

Course Page: Explore the Advanced Excel Course

Register for a Demo: Book Your Course Demo

Official Website: Visit JustAcademy