What’s A Good Use For Find And Replace In Excel?
Find and Replace can be a great way to clean up data in Excel. For example:
- To remove all # signs, replace # with nothing
- To remove all blanks, replace ” ” (a blank) with nothing
- To remove all dashes and everything after them, replace “-*” with nothing (the * serves as a wildcard)
- To remove all dashes and everything before them, replace “*-” with nothing
If you only want to clean up one column, just select that column first. The same thing if you only want to clean up one block of cells (just select the block of cells first).
The “replace with nothing” technique, for some reason, isn’t widely written about. I don’t recall reading about it anywhere, so may be I should patent it… It can save you a ton of time.
What Are Some Good Find And Replace Excel Shortcuts?
Here are three good shortcuts. The first two default to the Find tab.
Find & Replace Dialog Box – On “Find” Tab (keyboard #1): Ctrl+F
Find & Replace Dialog Box – On “Find” Tab (keyboard #2): Alt-E-E
Here’s what it looks like when you land on the Find tab:
To land on the Replace tab instead, use Ctrl+H:
Find & Replace Dialog Box – On “Replace” Tab (keyboard): Ctrl+H
Here’s what it looks like when you land on the Replace tab:
I personally don’t like Ctrl+H, because I prefer to start on the Find tab (and enter my characters), then go to the Replace tab. The flow seems more logical to me. (Note: you might also object that Ctrl+H requires the right hand to leave the mouse — but it really doesn’t matter, since you’ll probably be typing in the boxes with your right hand anyway…).
So which one is better for Find — Ctrl+F or Alt-E-E? They’re about equally fast, so pick whichever one you like.
How Do I Navigate The Find & Replace Dialog Box With Shortcuts?
Since you’ll be typing into the text boxes with both hands on the keyboard, you might as well learn a few quick shortcuts for moving around the dialog box (instead of using your mouse).
Once you’re on the Find tab, just type your input. Then, to go to the Replace tab, type Alt-P. This works because “P” is the hot key for the Replace tab — as you can see by the fact that the “P” in Replace is underlined.
Move To Replace Tab (keyboard): Alt-P
Once you’re on the Replace tab, type your input. When you’re done, you can execute the Replace All command by typing the hot key — which is “A” (which you can tell by the fact that the “A” in “Replace All” is underlined. After you do that, you’ll see something like the following:
So how do you exit out? Hit Enter, and then Escape.
Exit Dialog Box After Find/Replace Is Completed (keyboard): Enter-ESC
That’s it for the basics, and I hope these shortcuts save you some time.
As always, I welcome your input.