Following our last blog post, it’s time to look at the more advanced features of Find and Replace. Do you remember we left you last time around with the options expanded, as below.
Now what we’re going to do is use a couple of these advanced features at once.
At times when you have a large volume of data that requires the use of the Find and Replace function, rather than just making replacements you may wish to highlight where on the sheet the changes have been implemented.
Column E below indicates whether the product ordered is grated or not and the only options are Yes or No.
As you can see, though, in this instance there are some instances of a yes response without capitalisation. So we want to correct these from ‘yes’ to ‘Yes’.
Here is the normal method of find and replace but notice we are checking ‘Match case’. So it only picks the lower case version that is incorrect. The reason we do this will become clear.
What we want to do is highlight red any cells where a change has been made – this is great for big sets of data to ensure you know all that has been changed.
Here’s how we do it…Simply click the second Format button that is in line with ‘Replace with’.
You’re presented with the normal format cells dialogue box. On the fill tab, we select the colour of our choosing to highlight the cell. Then ‘Ok’ to exit the cell format box.
Now just click ‘Replace all’.
And here is the resultant effect.
Now this is clearly not the only use of the format option, the format option is also incredibly handy if you’ve been highlighting cells in a set of data a certain colour and now you want to change the format or contents of those cells.
Here I have filled a number of cells yellow.
If we go into Find and Replace (Ctrl+H) and make sure the options button has been pressed. We can now make changes to the cells and their contents based on the format.
This may be a surprise to some but we can actually leave the ‘Find what’ and ‘Replace with’ fields completely empty for this. But as you will see shortly leaving ‘Replace with’ blank does not mean it will force the value or text in the cell to disappear, when we change formats that is.
We will select the format of the cells we want to locate, by clicking the small downward arrow next to ‘Format’ this gives a separate drop down box below.
Selecting ‘Choose Format From Cell…’ returns you to the spreadsheet with a format picker mouse icon. You simply need to click a cell of the format you wish to amend.
You simply need to click a cell of the format you wish to amend.
Now you can decide what to do with the formatted cell. You can change the values in these cells or you can change the format or both.
Again you can pick from a cell or simply use the format cells dialogue box again.
We’re going to give these a border and change the colour.
The Find and Replace box will now look as so:
Let’s click the Replace All button…
Well, there you go that’s just some of the advanced features of Find and Replace, why don’t you have a play with some yourself.
Thanks for reading and please leave a comment if you found this blog useful, we love to know that we’re making a difference for you. We’re receiving some great feedback on LinkedIn and Facebook at the moment and with your help in sharing these blogs with your colleagues, friends and relatives we can make a real difference.