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.

   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.

   Checking for Messages in Cells
If you have a range of cells used to display error messages, you soon discover that it is easy to miss messages that may appear there. Here are some ideas on how you can draw attention to your error messages.

   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 Text in Text Boxes
Want to search for text that may appear in a text box rather than in a regular worksheet cell? You can only perform the operation using a macro, and a couple of such approaches are provided in this tip.

   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.

   Replacing Cell Formats
Need to replace the formats applied to some cells with a different format? Those using Excel 2003 will find it easy; those using an earlier version, not so easy. Here's how to accomplish 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.

   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 for All
When you are working on a worksheet (particularly a large one), you may want to search for and possibly copy information from many cells all at the same time. This is easier said than done in Excel, as this tip illustrates.

   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.

   Searching Through Many Workbooks
If you have a folder that contains dozens or hundreds of workbooks, you may need to search through those workbooks to find specific instances of text. This can seem like looking for a needle in a haystack. Here are some ideas on how you can make finding the desired information easier.

   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 by replacing a regular character with a superscript character.

   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.