Advanced Find & Replace in Excel | Data Cleaning

Advanced Find & Replace in Excel

Advanced Find & Replace operation in Excel is a powerful tool to search for the words in the documents and replace those words with a text or no text according to your need. Advanced Find & Replace is very quick then the basic Find and Replace operation.

Advanced Find & Replace will help you in more advanced search or detailed search. In this tutorial you will learn about Advanced Find & Replace techniques.

You might have seen 3 Idiot movie of Amir Khan. In that, he changed the speech of his co-star named Chatur. He changed few words of the speech using the Basic Find and Replace tool using the shortcut key CTRL + F. Well Advanced Find and Replace can be activated using the Excel shortcut key CTRL + H.

You can go through a long process of activating the advanced Find & Replace by using the shortcut CTRL + F and then clicking on the Replace tab key beside the Find Tab. So while discussing about the Advanced Find and Replace tool let us see what all criteria are going to be discussed.

Here we will discuss some of the Settings in the Advanced Find & Replace tool, wild card characters that helps us to Advanced Find and Replace, and the things we should not do.

Rather I will say things we should take care while caring out Advanced Find & Replace operations.

Let’s take a look with the help of the case study of Advanced Find & Replace

Case study 1: Using the wildcard character Asterisk (*) – Advanced Find & Replace

Some of the Name and Surname are listed down and now I want to separate these. We can use Text to Column to separate it. But the question arises can I replace these names with the help of Advanced Find & Replace?

Let us solve the first case study.

Will go through step by step explanation:

1. Will copy the data from cell A4: A9 to cell C4:C9. So that you can clearly differentiate or compare about what has happened.

2. We will select the data where we want to do advanced Find & Replace. Now as I do so a question might come into your mind as to why you select the data and not just the cell. And the Press CTRL +H.

Well, friends if I would have done so then the Advanced Find & Replace operation would have worked for that particular cell only.  So it is necessary to select the entire data and then press the Excel shortcut CTRL +H.

3. Here I want to delete the name. So I will write (,*) comma and Asterisk in Find what option. Find and Replace option starts searching from the left and it will search for the first comma in the data selected.

After (,) comma all the characters will be deleted. Because I have entered (*) asterisk, a wild card character. You can use (*: PDF) to search in windows and it will show you all the PDF that are available in the computer.

4. Right now I will click on Replace All. And whooo.. Good I will see all the names have disappeared.

Now, what if I wanted to replace Surname instead of Name then what should be done?

Come let’s see..

I will press Ctrl + H again and enter (*) asterisk first and then (,) comma in find what option. You can also add space if you want but that is not necessary. So now I have written (*,) and will click on Replace All.

Wohoo.. You just saw how fast name and the surname were separated or cleaned using the wild card character.

Case Study 2: Using the wild card character (?) Question Mark – Advanced Find & Replace

Consider that you are given a complex data and you just need name and surname and you do not need email id or network id codes along with name and surname. 

You will see that after every name there is a slash (/) and then some extra characters which are not required.

So if I want only Name and Surname I will follow this process.

1. Select the data and press the shortcut CTRL + H.

2. In the find what option will enter (/*) slash and the asterisk. And Click on Replace All.

Result: We will see that all the characters are deleted and we are only left with Name and Surname.  You just saw how fast we can clean the data using the wild card character.

We will Press CTRL + Z to go back to our original data. You now know about the Asterisk wild card character. Now what does question mark (?) mean? This is also a wild card character but in what way it is different from Asterisk (*).

When you look into the data you see that a pattern is followed. After every name there is slash (/) and two letter word again slash (/) and three letter word and so on. They are consistent everywhere in the data selected.

If you have a data that has a consistent length, you want to replace it with other word. Suppose in our data we want to replace three letter word with the other three letter word.

For example we want to replace M & C with ABC and also ADV with ABC.  Now if you would not know about using the wild card character to replace then you would have changed it individually for each cell. 

Now let’s see what is the use of Question Mark wild card character in Advanced Find and Replace?

If I write 3 question mark that means take all the words which contains three characters. While the Asterisk indicate any number of character. The whole of Mahabharat can come under this star (*).

Steps to use question mark wild card character:

1. Select the data and Press CTRL + H.

2. Enter (/???/) slash and three question mark and again a slash. So our advanced find and replace tool will test which length of characters contain Slash at the start and the end and in between three characters or letters.

3. Well it cannot take /IN/ from the example because it contains only two letters in between the two slash.

4. Will enter /ABC/ in the replace with option in the Advanced Filter & Replace. And click on Replace All.

Wohoo. That’s my Boy! This was the second trick in the Advanced Find & Replace option.

Case study 3: Using the Tidle key to lower the power of Asterisk (*) – Advanced Find & Replace

We learnt about some of the wild card character in our earlier data. Now how will I remove or clean the stars in my data?

I know you will quick press CTRL + H and in the find what option enter star (*) or asterisk and leave the replace with option blank. And press Replace All. You will see the entire data has disappeared.

You can do such when you are upset with your boss or you want all of your data to be lost. No no don’t do that or else it will have an adverse impact and you might lose your job and search for the new one.

So the question is how can you remove asterisk or question mark from the cell? Asterisk being a wild card character selects itself as well as other character in the data and so all the data got deleted. Let us see how to remove Asterisk or question mark from the data.

Steps are as follow:

1. Select the data and press Ctrl + H.

2. You will see a sign above the Tab key on your keyboard called as Tilde key (~). Name is different but it works great. It looks like a snake located above the tab key.

3. Enter the Tilde key in the find what option using the shift key. Enter (~*) Tilde key and Asterisk.

4. This benefits that the power of Asterisk is taken away and now it has become a normal character.

5. Press Replace All.

Result: Wohoo.. You will see that all the asterisk are removed from the data.

These were some of the Advanced Find & Replace Tricks and I wish you practice these tricks. It is not an end to the tricks on Advanced Find and Replace. Will show you some more tricks on Advanced Find and Replace in the next tutorial.

Just a short recap as to know what all did you learn in this tutorial.

1. The meaning and use of (/*) slash and Asterisk: Remove all the data starting from slash onwards (/)

2. The meaning and use of (/???/) slash and question mark: Replace certain number of letters with the other.

3. How to remove star or Asterisk or Question mark from the data: Neutralize the power of Asterisk using the Tidle Key (~)

Will be waiting for you in the text tutorial and some more tricks on Advanced Find and Replace.

Hope you like our tutorial for more cool and amazing trick of excel Like our Excel Superstar Facebook Page and subscribe to our Excel Superstar YouTube channel. Excel Superstar is the leading online training company, which provides Online Excel Course in Hindi. Connect with us and become an Excel Superstar.