Advanced Find and Replace

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.

Find and replace p2 section 1 -1

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.

Example

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

Find and replace p2 section 1 -2

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

Find and replace p2 section 1 -3

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.

Find and replace p2 section 1 -4

Now just click ‘Replace all’.

Find and replace p2 section 1 -5

And here is the resultant effect.

Find and replace p2 section 1 -6

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.

Example ii

Here I have filled a number of cells yellow.

Find and replace p2 section 2 - 1

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.

Find and replace p2 section 2 - 2

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.

Find and replace p2 section 2 - 3

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.

Find and replace p2 section 2 - 4Find and replace p2 section 2 - 5

The Find and Replace box will now look as so:

Find and replace p2 section 2 - 6

Let’s click the Replace All button…

Find and replace p2 section 2 - 7

In Summary

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.