Blog Replace Values ctrl and h

Hi Excel users, today’s blog is number one of a two part blog looking at the Find and Replace feature.

Problem

A common need in Excel is to replace multiple instances of text or values in a range of cells or a sheet. Perhaps for something as simple as changing your sheets for the new year.

Solution

The Replace function is found in the same place as the Find function we used in our last blog. You’ll find it under the Home tab of the Ribbon bar, under the Editing section, ¬†Find & Select, Replace.

Alternatively you could just use the shortcut, Ctrl+H.

Example

In the following example we have noticed, in our data of Cheese PLC, an incorrect spelling in the Country column. Can you spot it?? Rather than replace each instance of the error we can do a find and replace process, entering the correction only once.

Now as you can see I have highlighted the column by clicking the column letter C. This is because we do not want to replace all instances of the word Grate with Great. This as you hopefully have seen would impact column headings to the right, which in this case we do not want.

Replace values 1

Now if we use the shortcut mentioned earlier Ctrl+H we are presented with the Find and Replace dialog box.

Replace values 2

Here we have two main input fields (1) the data we want to find in order to replace and (2) the value or text to replace it with.

Now in the ‘Find what’ field we’ll enter Grate and in the ‘Replace with’ field we enter Great.

Replace values image 3

Then simply hit the Replace All button… hey presto! The contents of the cells now read Great Britain.

But hold on lets not stop there lets go back into the dialog box Ctrl+H and see what else we have to play with.  Just for fun lets hit the Options button. (this seems familiar from the last blog hey!)

Replace values 4 options

Some great features here and in the next blog from Exceptional Excel, we’ll be looking at the advanced options that come with Find and Replace and how to use some of these in the real world.

To ensure you won’t miss the excellent follow up blog from Exceptional Excel, sign up now and get it delivered direct to your inbox. Sign up here now.