Excel boasts powerful data analytics and organization tools. You can easily find and replace specific text or numerical values from any number of cells within a few seconds. Such tools and features make Excel the top spreadsheet application for data management.
The “Find and Replace” tool saves you a lot of time. It is not just limited to basic text; in fact, you can use it to find and replace cell formats, formulas, and even special characters.
Have you ever faced a situation where you have a large dataset and need to change specific words, numbers, or formats?
I am sure you have.
You manually search every cell for a specific word or number and then edit it, which might take forever.
In this article, we will explore various ways to use the Excel Find and Replace tool. We will learn to extend its capabilities with regular expressions, so make sure to read the article till the end.
Download The Example Excel Sheet
You can download the example Excel sheet containing the dataset we used to demonstrate the different ways to use a Find and Replace tool. Practicing the examples discussed in the following sections will strengthen your knowledge.
If you have your own spreadsheet ready with the required dataset, skip downloading the above file and follow me.
How to Use Find in Excel
The Find feature in Excel makes it easy to find a specific word, value, formula, and symbol in the worksheet.
Let us consider the following example where we have employee details, including their full name and department.
Our task is to find the keyword “IT” in the above table.
Here are the steps,
- Open the desired Excel spreadsheet
- Hover over the main menu and locate the “Home” tab
- Click on the “Find and Select” dropdown in the “Editing” group
- Select the “Find” option from the list
- A Find and Replace dialog box will appear on the screen, as shown below
- Enter the text you wish to find in the “Find what” box
(Since we are searching for “IT”, enter IT in the “Find what” box) - Click on “Find Next”
- Excel will jump to the first occurrence of the string that you are searching for
- Click on the “Find Next” button again to move on to other occurrences
- If you want to find the cell location of all instances, then click on “Find All”
- Click the “Close” button once you are finished
How to Use the Replace in Excel
There are some instances where you might need to update or correct specific data throughout your Excel spreadsheet. You can use the built-in “Replace” feature in Excel for this purpose. It allows you to replace text, numbers, or other data with new values.
You can use this feature to correct mistakes and update information.
We will be using the below example to demonstrate the functionality of this feature.
Our task is to change the department of Isla Wong from Accounting to Sales.
Let’s begin,
- Open the desired Excel spreadsheet
- Hover over to the main menu and go to the “Home” tab
- Click on the “Find and Select” dropdown
- From the dropdown menu, select the “Replace” option
- A Find and Replace dialog box will appear on the screen, as shown below
- Type the text you want to replace in the “Find what” box
(Since we wish to change the department of Isla Wong from Accounting to Sales, enter “Accounting”) - Enter the replacement text in the “Replace with” field
(Since we want to change Accounting to Sales, enter “Sales”) - Now, click on the “Find Next” button
- Excel will take you to the first instance
- Now, click on “Replace”
- Once the desired field is replaced with Sales, click on “Close”
Excel Find and Replace – Additional Options
For a more refined search, Excel provides you with several additional options beyond basic text and number replacements.
You can access these features by clicking on the “Options” button in the Find and Replace dialog box. Refer to the following image.
After clicking on the Options button, the dialog box will now look like below,
Here is an overview of the available options:
Within
Here, you need to specify where you want Excel to search for the desired string or number. It has two options:
- Sheet: Searches within the current workbook
- Workbook: Searches across all worksheets in the open workbook
Search
This field lets you specify how to search for a string.
- By Rows: Searches through rows from left to right
- By Columns: Searches through columns from top to bottom
Look in
This field determines where Excel should look for the search string.
- Formulas: Scans the formulas within the cell
- Values: Scans the value in the cell
- Formats: Performs a scan based on the formatting of the cells
- Comments: Searches within the cell comments
Match Case
You can check this box to find instances that match the exact case of the text or number to be searched.
Match entire Cell contents
Check this option only to find cells where the entire cell content exactly matches the search intent.
How to use the Find and Replace tool in Excel – With Examples
Now, let us discuss the steps to use the Find and Replace tool in Excel. We will consider different scenarios as below.
1. How to use the Find and Replace tool to update a text string with an exact match
Here’s the example data where we have employee names in the first column and their department in the second column.
Task: To replace the keyword “IT” in the entire spreadsheet with “Information Technology”.
Here are the steps,
- Open the desired Excel spreadsheet
- Hover to the main menu and click on “Home” tab
- Click on the “Find & Select” dropdown
- Select the “Replace” option from the list
- A Find and Replace dialog box will appear, as shown in the image below
- In the “Find what” field, type “IT”
- Enter “Information Technology” in the “Replace with” field
- Now, click on “Replace”
- Excel will replace the first “IT” keyword with “Information Technology”
- If you wish to make all the replacements at once, you can click on “Replace All”
- A small window popup saying “All Done. We have made [X] replacements” will be displayed
- Now, click on the “Close” button once done
It was a simple scenario where we did a case-sensitive search using the “Match case” option of the Find and Replace tool.
What is another way to access the Find and Replace tool in Excel?
You can access the Find tool either from the “Home” tab of the main menu or using the keyboard shortcut “CTRL + F” on Windows and “CMD + F” on Mac.
Similarly, you can access the Replace tool by pressing “Ctrl + H” on your keyboard.
2. Using the Find and Replace tool to update cells whose length is equal to the searched text string
Consider the following dataset where we have employee names in the first column, their job titles in the second column, and departments in the third column.
Now, for a few employees working in the IT department, the designation is written as “IT Systems Architect“.
Suppose we use the previous steps to replace the keyword “IT”, it will update “IT Systems Architect” to “Information Technology Systems Architect”.
But we don’t want it that way.
So, our task is to convert the “IT” keyword from the Department column to “Information Technology”.
Here, we need to use the additional option named “Match entire cells content” of the Find and Replace tool.
Let’s begin,
- Open a desired Excel spreadsheet
- Select any cell
- Hover to the main menu and click on “Home” tab
- Next, click on the “Find & Select” icon as shown below,
- Select the “Replace” option from the list
- In the Find and Replace dialog box, type “IT” in the “Find what” field
- In the “Replace with” field, enter “Information Technology”
- Click on the “Options” button as shown below to access the additional features
- Check the box before the “Match entire cell contents” option
- Now, click on “Replace All”
- You will observe a dialog box saying all the replacements have been done, as shown below
(This message means that there are a total of 20 employees working in the IT department. After using the Find and Replace tool, the department of 20 employees has been changed from “IT” to “Information Technology“) - Click the “Close” button to exit the dialog box
Now, after looking closely, you will notice that the cells containing “IT Systems Architect” haven’t been updated as we had configured the Find and Replace tool to search for the cells that include the “IT” keyword and have the same length as our search query.
Simply put, you have just replaced the cells, including the text string, as “IT” only.
3. Using the Find and Replace tool to update the cells using Wildcard characters
In this method, we will use the wildcard characters to find and replace a string. For example, when your spreadsheet contains multiple products that start with the letter “A,” you can use wildcard characters to identify products meeting specific criteria.
Here are the wildcard characters,
| Character Type | Description | |
|---|---|---|
| * | Asterisk | Helps you to find any string of characters. For example, you can use “fl*” to find fly and float |
| ? | Question Mark | Helps you find any single character. For example, “gr?y” can find Gray, and Grey |
For the sake of this article, let us consider a simple example as shown below.
Our task is to find all the employee names that start with “R” in each department. We have a record of 100 employees. Let us replace all those employee names with “classified”.
Here are the steps,
- Open a desired Excel spreadsheet
- Select any cell
- Hover to the main menu and click on “Home” tab
- Select the “Find & Select” dropdown
- Select the “Replace” option from the dropdown
- In the Find and Replace dialog box, type “R*” in the “Find what” field
(Make sure to put the asterisk symbol after the letter) - In the “Replace with” field, enter “classified”
- Click on the “Options” button to see all the additional features
- Check the box labeled “Match entire cell contents”
- Now, click on “Replace All”
- You will observe a small dialog box saying all the replacements have been made
- Now, click on “Close” to exit the Find and Replace dialog box
4. Using the Find and Replace tool to search a text string within the formula
Did you know Excel allows you to use the Find and Replace tool to search for the text string within a formula?
We have employee data with their salaries and holiday bonuses. Those who have received the bonus are marked with “Yes” in the fifth column named “Bonus Received”.
It uses a formula to identify the employees who have received the bonus.
=IF(D2>0,”Yes”,”No”)
Refer to the following image,
Our task is to replace the keyword “No” with “Better Luck Next time”.
Here are the steps,
- Select any cell of the spreadsheet
- Hover to the main menu and click on “Home” tab
- Choose “Find and replace” dropdown
- Click on the “Replace” option as shown in the image below
- Type “No” in the “Find what” field and “Better Luck Next Time” in the “Replace with” field
- Click on the “Options” button
- In the “Look in” dropdown menu, select “Formulas” as shown below. This tells Excel to search the search term specified within the formulas
- Click “Find Next” to locate the first instance of the text string within the formulas.
- Now click on “Replace All” to change all the values
- A small dialog box will appear on your screen saying, “We made [X] replacements”
- Click on “Close”
This method is beneficial when you need to update or audit specific parts of your formulas across a worksheet or workbook.
Conclusion
That’s all about using the Find and Replace tool in Excel. It is a really powerful tool allowing users to replace any number of instances at once within a few seconds.
Practice is the key here. You will master this tool with practice. So make sure to play around with the example Excel sheet you downloaded at the beginning of this article.
Feel free to comment below if you are stuck somewhere or having any particular issue while using the Find and Replace tool in Excel.
Explore our blog section for more tips and tricks to use Microsoft Excel and Google Sheets like a pro.