This article is a complete guide to the Find and Replace in Excel. We will discover the new tips and tricks that can be done with the Find & Replace tool. This tool can be of huge advantage to you than just finding and replacing values from a large database in Excel.
Steps to Find and Replace in Excel
Let us first learn about the basic usage of this tool, step-by-step.
Finding a value in Excel
We take an example of a small database below to learn more.
To find or lookup a value in Excel, do as follows next.
- Press CTRL+F, or
- Go to the Home tab.
- Under the Editing section, pull down on Find & Select.
- Click Find Next.
A window named Find and Replace opens.
- Type the value that you want to find.
- Click Find All.
- Click Options if you still cannot find what you’re looking for.
- Select Sheet if you want to find a value within the sheet or select Workbook if you want to find a value within the entire workbook.
- You can select the to look in formulas, values, or comments in the Look in box.
Note that Values stand for text and numerical contents in Excel.
- We will find the value “fireflare” in the sheet.
- Click Find All to find all values matching your search term in the sheet.
- Click Find Next to find the first value that matches your search term. Keep clicking this button to find the next value matching your search within the sheet or workbook.
Once you click Find All, you can see that the window extends below to show the exact cell address of all the values found within the sheet.
It displays the workbook name, sheet name, cell address, formula (if any), and value in the cell.
- Click Close when you’re done.
Finding and replacing a value in Excel
To find and replace a value in Excel, do as follows next.
- Press CTRL+H, or
- Go to the Home tab.
- Under the Editing section, pull down on Find & Select.
- Click Replace.
The Find and Replace window opens one again but this time you’re in the Replace tab.
- Type the value that you want to find in the Find what box.
- Type the value that you want to replace in the Replace with box.
- Click Replace to replace the first available value.
- Click Replace All to replace all values found in the sheet or workbook.
- Remember that the values put in the Replace with box are case-sensitive. Anything you write in Replace with appears the same way on the sheet.
- Click Options to view more options.
- Similar to the Find tab, you can select if you want to find and replace a value within the entire sheet or the entire workbook, search in rows or columns, and look in from different types of contents.
You can see that the company name Fast-Hats Co. has been replaced with the name Gleeway Co. successfully with the help of the replace option.
Removing line breaks in a cell with find and replace
With the Find and Replace tool you can remove line breaks from a cell in Microsoft Excel. Line breaks can be added by pressing ALT+ENTER where you want the line to break from.
Also read: How to Insert or Remove Page Breaks in Excel?
Here’s how we can remove all line breaks from a cell.
- Click or select the cells with line breaks in them.
- Press CTRL+H, or
- Go to the Home tab.
- Under the Editing section, pull down on Find & Select.
- Click Replace.
- In the Find what box, press CTRL+J on your keyboard.
- Notice a blinking underline shows up in the box. This means we’re finding line breaks.
- Leave the Replace with box blank.
- Click Find Next or Find All to find line breaks in the sheet or workbook as per your settings.
- Click Replace to remove line breaks from the selected cells.
You can see that all line breaks have been removed from the cell and the text is in a continuous flow.
Regex Find and Replace in Excel
Excel supports finding and replacing values with the use of RegEx or regular expressions. If you do not have a basic understanding of Regex, visit this website to try out and learn how regex works.
Let’s see how we can do it using Find and Replace.
Also read: How to Use Flash Fill in Excel?
We have a list of patients here with their patient’s code. Our goal is to remove the patient codes from beside their names. Let’s get started!
- Select the cells with patient names.
- Press CTRL+H, or
- Go to the Home tab.
- Under the Editing section, pull down on Find & Select.
- Click Replace.
- Type (*) in the Find what box.
- This means that we are finding all values that start and end with brackets and an asterisk in the middle indicates any number of characters between the brackets. An asterisk stands as a wildcard indicating any random number of characters.
- Leave the Replace with box blank.
- press Replace All.
You can see that the patient list is now free of the patient codes.
Conclusion
This was all about the Find and Replace tool in Excel. Feel free to comment below if you have any doubts regarding the Find and Replace tool in Excel.
References: TrumpExcel