Find and Replace

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.

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 and Deleting Links
A VBA macro to find and delete external links.

Finding and Replacing Error Values
Want to get rid of error values in your worksheet? There are a couple of ways you can approach this particular challenge, as discussed in this tip.

Limiting Searching to a Column
Find and Replace is one of Excel's most-used tools when working with large amounts of data. You can also limit the area searched by Find and Replace by simply selecting the area you want searched before you start the search.

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 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 have line breaks in some of the cells of your worksheet, you might want to search for them. There is no "special character" you can use for the search, like there is in Word. You can easily find the line breaks by using an ASCII code in the search parameters.

Searching for Wildcards
When using Find or Find and Replace, you can use the question mark and asterisk as wildcards in your search. If you want to search for those actual characters, you must preface them with a tilde, as explained in this tip.

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—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.

 

More Information

The following are additional topics related to the subject of 'Find and Replace'. A bracketed number after the topic indicates how many articles are related to that subject.