How to Replace Words in Excel (and avoid unnecessary mistakes)

How to Replace Words in Excel

Would you like to know how to replace words in Excel in a convenient way and avoid unnecessary mistakes? Then you’re in the right place.

Keep watching to learn more!

Would you rather watch this tutorial? Click the play button below!

How to Use Find and Replace Feature in Excel

If you need to search data for a specific word or phrase and replace it with something else, Excel offers a useful Find and Replace feature.

Let’s say we want to change the name of the department from ‘HR’ to ‘Human Resources’ throughout this data table.

How to Replace Words in Excel - Change the name of the department from ‘HR’ to ‘Human Resources’

To do that, we need to open the Find and Replace feature in the Home tab by clicking on ‘Find and Select’ and choose ‘Replace’.

How to Replace Words in Excel - Home Tab
How to Replace Words in Excel - Find and Select - Replace option

Now we see a pop-up window where we need to specify two details, which are: what to look for – we’ll enter the information in the field ‘Find what’, which is ‘HR’ here – and what should be used instead of this word or phrase – so in the field ‘Replace with’, we’ll enter ‘Human Resources’.

How to Replace Words in Excel - Find what - Replace with

This is the way to direct Excel to search through the table and wherever the phrase ‘HR’ occurs, to replace it with ‘Human Resources’.

Now, there’s one thing to watch out for. Excel goes through each and every occurrence of the phrase within the data table. The problem is that the table might contain the letters ‘HR’ as part of other words and in such cases, we don’t want to replace them.

For example, ‘HR’ appears as part of the surname ‘Hrabal’ in the cell C3.

How to Replace Words in Excel - Hrabal

If we hit ‘Replace all’, Excel will change all ‘HRs’ to ‘Human Resources’ in the column ‘Department’, but it’ll also replace the letters ‘Hr’ in the name ‘Hrabal’ in C3, which is not quite right.

How to Replace Words in Excel - replace the letters ‘Hr’ in the name ‘Hrabal’

How to Replace Letters only in the Selected Phrases

So, let’s undo the changes now and have a look at how we could fix this and replace the letters ‘HR’ only in those phrases where we actually want to have them replaced.

How to Replace Words in Excel - Undo the changes

First click on ‘Find All’ here at the bottom, and Excel will list all occurrences of the searched phrase.

How to Replace Words in Excel - Click on Find All

Then click on ‘Value’ here in the header, which will sort the items on the list. You can see that we’ve got all the relevant phrases grouped together now.

How to Replace Words in Excel - Click on Value
How to Replace Words in Excel - the relevant phrases grouped together now

Then click on the first item, press and hold the Shift key and finally click on the last ‘HR’ on the list. That’s how we select only the phrases we really want to change.

How to Replace Words in Excel - click on the first item, press and hold the Shift key and finally click on the last ‘HR’ on the list
How to Replace Words in Excel - selected only the phrases we really want to change

Once done, you can simply click on ‘Replace All’ and Excel replaces only the selected phrases. The name ‘Hrabal’ has been left unchanged just as we wanted.

How to Replace Words in Excel - Click on Replace All
How to Replace Words in Excel - Excel replaces only the selected phrases
How to Replace Words in Excel - The name ‘Hrabal’ has been left unchanged

Don’t miss out a great opportunity to learn:

If you found this tutorial helpful, give us a like and watch other tutorials by EasyClick Academy. Learn how to use Excel in a quick and easy way!

Is this your first time on EasyClick? We’ll be more than happy to welcome you in our online community. Hit that Subscribe button and join the EasyClickers!

Thanks for watching and I’ll see you in the next tutorial!

Comments

Add comment

Your email address will not be published.

Your personal information will only be used for the purpose of processing this comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed.