Excel’s find and replace allows you to search for something (a text, a number, all formulas, etc) and replace it with something else. This blog post gives an entire overview of all options there are when finding and replacing in Excel.
There are quite a few tricks that you can use with the searching tools of Excel. You can limit your search to only dates, for example. Or you can use wildcards to broaden your search. You can even specify a formatting and find all cells that are formatted that way.
We’ll start with finding and we’ll get to replacing after that. Let’s do it.
Open and understand the Find window
You can open the Find window by clicking on Find & Select on the Home tab of the Ribbon and then clicking on Find… in the dropdown. Alternatively you can press Ctrl + F (Windows) or ⌘Command + F (Mac).
Opening the find window in Excel.
The window that opens is quite simple looking. In the Find what: field you can type something to search for. Click on Find Next to find the first occurrence of the thing you searched for. Click on Find Next again to find the second occurrence.
The Find window.
Choosing Find All will result in a list with all occurrences. You can click on any of them to go to that specific search result. You can also press Ctrl + A (Windows) or ⌘Command + A (Mac) to select all results. With all results selected, you can of course do all sorts of editing on them: delete them, format them, copy them, etc.
Note: by default, searching is case insensitive. So if you search for HARRY you will also find Harry, hARRY and harry.
If you’d like Excel to search with case sensitivity enabled, you can do so from the extra options, but we’ll get to that later in the article.
Excel wildcards are special characters that allow you to search for more than an exact word match.
For example, you may want to look for words starting with the letters sa. You can use the search term sa* for this. Any cells containing words like Sasha, Sara, Sam or Samantha would be found. The asterisk * is the wildcard that notifies Excel that you allow any number of other characters at that position.
The asterisk can also be used in the middle of the search term: sa*a would still find Sasha, Sara and Samantha, but would not find Sam. That’s because Sam doesn’t contain a second a.
The question mark
There are more wildcards, like the questions mark ?: a wildcard that requires any single character at that position.
Sa?a would find the words Sara and Samantha, but not Sasha or Sam. It would not find Sasha because there are two characters (the s and the h: sasha) between sa and a.
Note: the question mark wildcard requires that there is a single character there. So for example searching for sa?a will not find the word saab because there is no character between the first and second a.
The tilde is an easy one. Suppose you want to use the asterisk * or the question mark ? themselves in a search. How would you search for them?
For example, searching for When? will not look for the literal word When?. It will instead look for When and one wildcard character. That’s what the question mark is designed to do.
If you want to negate this effect, you have to put a tilde ~ in front the wildcard. So for this example, you would write When~?. That would make Excel search for the literal word When?.
Use more searching options
Click on Options > > to see more search options.
The Find window showing more options.
The extra options you can choose are:
- Search for certain formatting.
- This allows you to look at only cells with a red border for example. However, it also includes number formatting, so you can look for all dates containing 5 without finding other numbers containing 5.
- Search within the worksheet or the workbook.
- Exactly what you would expect. Limits your search to a sheet or includes the entire workbook.
- Search by columns (top-to-bottom) or by rows (left-to-right).
- Both methods will find the same results, but they might find them in a different order. If you have a very large dataset, this could be a useful feature for you.
- Search in formulas, values or comments.
- This option can be a root of a lot of confusion if you can’t seem to find the formula or result you’re looking for. Suppose you have the formula =1+1. If this option is set to values you have to search for the formula result to find it. So in this case you would search for 2. If you have the option set to formulas, you should search for the literal formula, so =1+1.
- Turn on case sensitivity.
- Simple. Searches for Harry will only match with Harry. Not with HARRY, hARRY or hArRy.
- Turn on exact matching of entire cell contents.
- Also simple. Searches for Sara will only match with Sara. Not with Sara Smith, My name is Sara or Sarah.
That’s all for the Find window. You really know quite a bit about it now. Well done!
Find and Replace
Open the Find and Replace window
Open the Find and Replace window by clicking on Find & Select on the Home tab of the Ribbon and then clicking on Replace… in the menu that opens. Or you can press Ctrl + H (Windows) or Command + H (Mac) as well.
Opening the Find and Replace window from the RIbbon.
About Find and replace in Excel
Finding and replacing works almost the same as just finding things. The window is almost exactly the same. All that’s new is that you now replace things with a value of your choosing. For information on the options in the window, you can look at the Find section above.
The Find and Replace window.
There are two useful replacing tips that we will share with you though.
First of all, removing the things you find is as simple as replacing them with nothing. Fill in your search term, leave the Replace with: field empty and click on Replace all.
Replace line breaks
Second, replacing line breaks. If you didn’t know: a line break is when a new line is started. It is also known as an Enter, a carriage return or a line feed. You can insert a line break in a cell by pressing Alt + Enter.
Replacing line breaks is as simple as pressing Ctrl + J (Windows only) in the Find What: field.
After reading this, searching and replacing things in Excel should no longer have any secrets for you. If there are any other tips that you have regarding Find & Replace, leave them in the comments below.