Advanced Excel Find and Replace

2. Find & Replace – Using Wild Card Characters

3. Find & Replace – Removing Wild Card Characters

1. Find & Replace – Basics

I have a column which contains the Surname and Name of the persons. Now I want to delete the surname from the list. Here I can delete using text to column, but to do it we will be using Find and Replace

1. Copy the data and paste it to the right-side to compare the data

2. Choose the Entire Data

3. Press the Shortcut Key Ctrl + H

4. Find and Replace Panel will open. Now we want to delete the Surname from the column

5. In find what section press comma (,) and Asterisk (*)

Note – Find and Replace starts searching from left to right and looks for the first comma. After comma (,) it will delete all the characters because we have applied a wild card character called Asterisk

6. Click on Replace All > Ok button

7. Lastly your surname will be deleted from the list

But here, if I want to delete the names and not surnames, then also we will be using Find and Replace by following the steps given below:

1. Choose the entire data

2. Press the Shortcut Key Ctrl + H

3. Find & Replace Panel will open. Now we want to delete the Name from the column

4. In find what section press Asterisk (*) and then press Comma (,)

Note – Pressing Asterisk and then comma will delete all the characters before the comma in the given column

5. Click on Replace All > OK button

6. Lastly your name will be deleted from the list

2. Find & Replace – Using Wild Card Characters

1. Asterisk (*)

I have data consists of Name, Surnames, Email ID, and Network ID. Now I want to remove all the Email ID’s and Network ID’s from the data. Here in this data after, every name, there is Slash (/) and extra characters.

So if we want to remove all the Network ID’s and Email IDs, then we will be using Find and Replace

1. Select the Entire Data

2. Press the Shortcut Key Ctrl + H

3. Find & Replace Panel will open. Now we want to delete all the Email ID’s and Network ID’s from the column

4. In find what section press Slash (/) and press Asterisk (*)

Note – Pressing Slash and Asterisk and will delete all the characters after the slash in the given column

5. Click on Replace All > OK button

2. Question Mark (?)

In this table, I have a series of patterns that contains Name, 2 letter word and, then 3 letter word, which is consistent in the entire column.

Now, if you want to replace the 3 Letter Word with ABC in the entire column. So to replace it, we will be using Question Mark (?) wild card character in Find & Replace

1. Select the Entire Data

2. Press the Shortcut Key Ctrl + H

3. Find & Replace Panel will open. Now we want to replace the 3 Letter word with ABC

4. In find what section press Slash (/), 3 Question Mark (???) and again Slash (/)

Note – Find and replace will search the length of characters which contain Slash (/) at the beginning, 3 Characters in the Middle and Slash (/) at the end.

5. In replacing with section put Slash (/), 3 Characters (ABC), again Slash (/)

6. Click on Replace All > OK button

7. Lastly, your characters will be replaced using Question Mark (?) wild card character.

3. Find & Replace – Removing Wild Card Characters

Here I have data that consists of Wild Card Character Asterisk (*). Now I want to remove the wild card character from the real data. So to remove it we will be using Find & Replace

1. Select the Entire Data

2. Press the Shortcut Key Ctrl + H

3. Find and Replace Panel will open. Now we want remove the Wild Card Characters.

4. In find what section press the tilde key (~) and press Asterisk (*)

Note – Tilde key (~) will be found on the upper side of the Tab key in your keyboard

5. Click on Replace All > OK button

6. Lastly, your wild card characters will be removed