Excel's Find and Replace function can quickly make major revisions to your sheet in just a few steps. You can add, remove, or replace data with the comprehensive Find and Replace tool. Learn how to utilize this powerful feature in Excel with the following articles.

Tips, Tricks, and Answers

The following articles are available for the 'Find and Replace' topic. Click the article''s title (shown in bold) to see the associated article.

   A Fast Find-Next
Tired of the Find and Replace dialog box blocking the view of your worksheet when you are searching for information? Do what I do and get rid of the dialog box. Use the technique described in this tip to do your searching.

   Allowing for Prefixes and Suffixes in Find and Replace
Excel includes a rather simplistic find and replace capability. If you have more complex needs, you'll need to seek out alternatives. This tip covers some other ways you can meet your needs.

   Changing Default Search Settings
Excel provides some great tools for finding information in a worksheet or a workbook. Changing the default settings used for a search can be a challenge, as illustrated in this tip.

   Changing the Color Used to Highlight Found Information
When you want to find information in worksheet, Excel can handily locate and highlight that information. If you find the highlighted information difficult to see, then you'll appreciate the information provided in this tip.

   Counting the Results of a Formula Using Find and Replace
Need to get a count of a particular result from a formula? You can use Find and Replace (as described in this tip), but that may not be the best way to get the result you want.

   Deleting All Characters Up Through a Unique Character Sequence
If you need to delete information of unknown length at the beginning of your cells, there are a couple of ways you can accomplish the task. This tip looks at two ways, including just using Find and Replace with a simple wildcard character.

   Deleting Dates within Text Strings
Finding and replace dates contained within other text in a cell can be a bit tricky. This tip looks at some approaches to deleting dates and leaving whatever is left.

   Find and Replace in Headers
Using Find and Replace is something quite routine in Excel, as it easily allows you to find and replace information in your worksheets. What if you want to find and replace information in headers or footers, however? That isn't quite as easy.

   Finding All Instances of a Value
Searching for information in an Excel worksheet generally goes very smoothly. There can be times, however, when the idiosyncrasies of the program can make it hard to understand why Excel finds some things and not others. Here's an example of such a situation and what can be done about it.

   Finding and Replacing in Text Boxes
Finding and replacing information in a worksheet is easy. Finding and replacing in other objects (such as text boxes or chart labels) is a different story entirely. This tip examines ways you can find and replace information in these other items.

   Finding and Replacing with Subscripts
Want to use Find and Replace to change the formatting of a cell's contents? You would be out of luck; Excel won't let you do it. Here's a macro that can shed light on how to do the formatting, though.

   Finding Based on Displayed Results
Want to use Excel's Find feature to locate cells based on what those cells display? It's easy if you know how to adjust where Excel looks for matches.

   Finding Boolean Values
Excel worksheets can contain all sorts of data. One thing you might store in a worksheet is a range of Boolean (TRUE or FALSE) values. This tip explains how to go about searching for these types of values.

   Finding Cells Filled with a Particular Color
Do you need to find cells that are formatted with a particular color? How you accomplish this task depends on your version of Excel.

   Getting a List of Matching Cells
The Find and Replace capabilities of Excel allow you to easily locate all the cells in a worksheet that contain specific values. If you want to copy the list of addresses to a different worksheet, though, the task is a bit more complex. Here are some ideas you can apply.

   Inconsistent Behavior of Find and Replace Dialog Box
The Find and Replace feature in Excel is one of the workhorse editing tools you can use. When the Find and Replace dialog box is displayed, the contents of the Find What box should be selected automatically. If they are not, that can lead to frustration.

   Limitations On Finding Characters
When you search for information in a worksheet, you expect Excel to return results that make sense. If you don't get a search result that matches what you can see in your worksheet, it is possible you are trying to do something that exceeds what Excel was designed to do.

   Limiting Searching to a Column
When you use Find and Replace, Excel normally looks through all the cells in a worksheet. You may want to limit the search to a single column, however. Here's how you do it.

   Making All Occurrences Bold
Want to make instances of a given word or phrase bold throughout a worksheet? Here's a way you can make the change quickly.

   Removing Cells Containing Specific Terms
Macros are great for processing large amounts of data quickly. This tip examines several ways you can remove specific terms from a range of cells containing those terms.

   Replacing Cell Formats
Need to replace the formats applied to some cells with a different format? You can use Excel's Find and Replace tool to make the switch.

   Replacing Characters at the End of a Cell
The Find and Replace capabilities of Excel can come in handy, but they can't accomplish all your replacement needs. One such situation is when you need to modify information that occurs at the end of cell. Here's some ideas on how you can replace the characters using different approaches.

   Replacing in Worksheets and Comments At the Same Time
If you need to replace information that may appear in cells, comments, and text boxes, your best bet is to use a macro. This tip provides such a macro, along with a suggestion that could be used to bypass using a macro.

   Replacing Only Whole Words in Excel
Excel's Find and Replace capabilities are handy, but they aren't as full-featured as those in Word. One shortcoming is the inability to find entire words when those words are within text strings. Here are a couple of approaches that can help you work around this limitation.

   Replacing Tildes at the Beginning of a Cell
Replacing a specific character (such as a tilde) seems a simple task, until you need to replace it only in a certain character position. Here's a way to replace only leading tildes in cells.

   Searching a Workbook by Default
When you display the Find tab of the Find and Replace dialog box, you'll notice that any search, by default, will be on the current worksheet. If you want Excel to default to searching the entire workbook, you'll need to resort to a macro to accomplish the task.

   Searching by Columns, by Default
Do you often want to search through a worksheet by column rather than by row? Excel defaults to searching by row, of course, so you need to look for a way to instruct it to search in the order you prefer.

   Searching Comments
Need to find that misplaced comment in your worksheet? It's easy to do using the Find and Replace capabilities of Excel.

   Searching for Leading Apostrophes
Take a look at the Formula bar when you select a cell that contains text, and you may see an apostrophe at the beginning of the text. Searching and replacing those apostrophes can be frustrating, until you know what they are for.

   Searching for Line Breaks
If you need to find where line breaks are located in cells, there are a couple of ways you can proceed. Here's a quick overview of how you can locate those pesky line breaks.

   Searching for Wildcards
Wildcard characters can be used within the Find and Replace tool, but what if you want to actually search for those wildcard characters themselves? That's where the judicious use of the tilde character comes into play.

   Superscripts in Find and Replace
The find and replace used in Excel is less powerful than its counterpart in Word, so it is not able to do some of the character-level replacements you might desire. Here's how to do one type of this formatting--replacing a regular character with a superscript character.

   Using Find and Replace to Find Conditionally Formatted Cells
You can use Find and Replace as a quick way to count any number of matches in your document. You cannot, however, use it to count Conditional Formatting results. This tip examines what that is and provides a different way to count your matching cells.

   Using Find and Replace to Pre-Pend Characters
Need to add some characters to the beginning of the contents in a range of cells? It's not as easy as you might hope, but here are some great ideas.

   Wildcards in 'Replace With' Text
When doing searches in Excel, you can use wildcard characters in the specification of what you are searching. However, you cannot use them in the replacement text. This tip examines ways you can work around this limitation and use wildcards in your replacements.