You probably know that you can use Find and Replace to replace multiple occurrences of text.
Ctrl-F, and then click on the Replace tab, or Ctrl-H to go straight to the Replace tab.
Or to replace text with nothing:
Here’s a couple of other useful ways you can use this command.
Replace leading zeros or spaces
You’ve imported text from another programme, but you have data that is out of alignment, because Excel has inserted zeros or spaces in front of the numbers. For example, 00015 or ( 15). Simply enter the number of 0s or spaces in the find box, leave the replace box blank, then Find All.
BE CAREFUL: You may replace correct text inadvertently – for example, letters or spaces in the middle of words. Thank goodness for the undo button!
Correcting formatting within words
For example, you want to change all occurrences of the word “Moe” with MOE. Enter “oe” in the “Find what:” box, and OE in the “Replace with:” box. Match Case.
BE CAREFUL: You might find that every instance of the word “does” now looks like “dOEs”, so use it carefully.
Changing the formatting of selected cells
You might need to change, for instance, all the green shaded cells with orange, or all the bold type with italics.
Ctrl-H > Options
Click right on the Format button if you know the format you want to change or Format dropdown > Choose Format from Cell… An eyedropper appears. Click on the cell whose format you want to change. This way you get the exact colour/format that you need to change.
Choose the format you want to change to. Replace All.
We can help you with your Excel questions. Let us know if you need more help.
Thanks to Trumpexcel.com for the screen shots.
If you have any other questions, Contact us!
