FIND SEARCH SUBSTITUTE and REPLACE Practice Questions with Solutions

Introduction

Excel text functions are very useful when you need to find, replace, or modify specific parts of text. FIND locates text with case sensitivity, SEARCH finds text without case sensitivity, SUBSTITUTE replaces matching text, and REPLACE replaces characters based on their position. In this chapter, you will practice these functions using names, email addresses, product codes, phone numbers, addresses, and business data. Excel-FIND, SEARCH, SUBSTITUTE and REPLACE practice questions with solutions to help you understand the concepts.

Question 1: Find the Position of a Character Using FIND

Problem Statement

Find the position of the @ symbol in each email address.

Excel Data

Solution

In B2, enter:

=FIND("@",A2)

Copy the formula down.

Expected Output

FIND returns the position where the searched text starts.

Question 2: Find Text Without Case Sensitivity Using SEARCH

Problem Statement

Find the position of the word excel in each sentence. The word may appear in different capitalization.

Excel Data

Solution

In B2, enter:

=SEARCH("excel",A2)

Copy down.

Expected Output

SEARCH is not case-sensitive, so Excel, EXCEL, and excel are treated as the same text.

Question 3: Replace a Word Using SUBSTITUTE

Problem Statement

The word Delhi needs to be changed to Noida in each address.

Excel Data

Solution

In B2, enter:

=SUBSTITUTE(A2,"Delhi","Noida")

Copy down.

Expected Output

SUBSTITUTE searches for specific text and replaces it with new text.

Question 4: Replace Characters by Position Using REPLACE

Problem Statement

Employee IDs are stored as:

EMP-101

Replace the first three characters EMP with STU.

Excel Data

Solution

In B2, enter:

=REPLACE(A2,1,3,"STU")

Copy down.

Expected Output

REPLACE works according to character position rather than searching for a particular word.

Question 5: Replace Multiple Occurrences Using SUBSTITUTE

Problem Statement

A product description contains the word Old. Replace every occurrence of Old with New.

Excel Data

Solution

In B2, enter:

=SUBSTITUTE(A2,"Old","New")

Copy down.

Expected Output

By default, SUBSTITUTE replaces every matching occurrence.

Question 6: Replace Only a Specific Occurrence Using SUBSTITUTE

Problem Statement

Some product codes contain multiple hyphens. Replace only the second hyphen with /.

For example:

ELEC-LAP-2026

should become:

ELEC-LAP/2026

Excel Data

Solution

In B2, enter:

=SUBSTITUTE(A2,"-","/",2)

Copy down.

Expected Output

The last argument 2 tells Excel to replace only the second occurrence.

Question 7: Find a Word Using FIND and Extract It with LEFT

Problem Statement

Extract the username from each email address. The username is the text before @.

Excel Data

Solution

In B2, enter:

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

Copy down.

Expected Output

Here, FIND locates @, and LEFT extracts everything before it.

Question 8: Replace a Part of a Phone Number Using REPLACE

Problem Statement

Phone numbers have an incorrect country code:

91-9876543210

Replace 91 with +91.

Excel Data

Solution

In B2, enter:

=REPLACE(A2,1,2,"+91")

Copy down.

Expected Output

REPLACE changes characters according to their positions.

Question 9: Replace Text Based on a Found Position

Problem Statement

Product codes contain the department code at the beginning:

IT-LAP-101

Change the department code IT to CS.

Excel Data

Solution

In B2, enter:

=REPLACE(A2,1,FIND("-",A2)-1,"CS")

Copy down.

Expected Output

This formula combines REPLACE and FIND.

FIND("-") identifies where the department code ends, so the formula can replace it even if its length changes.

Question 10: Clean and Standardize Text Using FIND, SEARCH, SUBSTITUTE and REPLACE

Problem Statement

A company has product descriptions with inconsistent information.

Perform the following tasks:

1. Replace Old with New.
2. Replace Delhi with Noida.
3. Find the position of -.
4. Replace the first three characters of the product code with NEW.

Excel Data

Solution

Updated Description

In C2, enter:

=SUBSTITUTE(SUBSTITUTE(B2,"Old","New"),"Delhi","Noida")

Copy down.

Updated Code

In D2, enter:

=REPLACE(A2,1,3,"NEW")

Copy down.

Expected Output

This final exercise combines multiple text-cleaning techniques into a practical data-management task.

Key Takeaways

• FIND returns the position of text within another text value.
• FIND is case-sensitive.
• SEARCH returns the position of text without considering letter case.
• SUBSTITUTE replaces specific text with new text.
• SUBSTITUTE can replace every occurrence or a particular occurrence.
• REPLACE replaces characters based on their position.
• FIND and LEFT can work together to extract text before a specific character.
• FIND can also be combined with REPLACE when the location of text is variable.
• SUBSTITUTE is useful when you know the exact text that needs to change.
• REPLACE is useful when you know the character position that needs to change.
• These functions are useful for cleaning product codes, addresses, email addresses, phone numbers, and imported data.

FAQs

1. What is the FIND function in Excel?

FIND searches for one piece of text inside another and returns its starting position.

=FIND("@",A2)

It is case-sensitive.

2. What is the SEARCH function in Excel?

SEARCH also finds text and returns its position, but it is not case-sensitive.

=SEARCH("excel",A2)

3. What is the difference between FIND and SEARCH?

The main difference is case sensitivity.

For example:

=FIND("Excel",A2)

distinguishes between Excel and excel, while:

=SEARCH("Excel",A2)

does not.

4. What does SUBSTITUTE do in Excel?

SUBSTITUTE replaces specific text with another text.

=SUBSTITUTE(A2,"Delhi","Noida")

5. Can SUBSTITUTE replace only one occurrence?

Yes. The optional fourth argument specifies which occurrence should be replaced.

=SUBSTITUTE(A2,"-","/",2)

This replaces only the second -.

6. What does REPLACE do in Excel?

REPLACE replaces characters according to their position.

=REPLACE(A2,1,3,"NEW")

This replaces three characters starting at position 1.

7. What is the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE searches for specific text, while REPLACE works with character positions.

8. Is FIND case-sensitive?

Yes. FIND is case-sensitive.

For example, searching for "Excel" is different from searching for "excel".

9. Is SEARCH case-sensitive?

No. SEARCH is not case-sensitive.

It treats Excel, EXCEL, and excel as the same search text.

10. Can FIND and SUBSTITUTE in excel be used together?

Yes. They can be combined when you need to locate text and then use its position or other text functions to modify the value.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar