How to Find and Replace Wildcard Characters in Excel

Finding and replacing required text and numbers is very easy with the use of Wildcard Characters in Microsoft Excel. It might be single occurrence or multiple occurrences – everything can be found and replaced easily using Wildcards. There will be a situation that, rather than using the wildcard characters for searching text starting with, containing or ending with, you may want to search for that particular wildcard character and replace it with the text you want.

Find and replace Wildcard characters in Excel

So, it means that if we want to search for wildcard characters as normal text, then there will be a different way to do it.

Let us take the sample data,

The*windows club

The Windows Club

The windows*club

find wildcard characters in excel using tilde

Here I want to find the ‘*’ symbol and replace it. If you are not aware of how to do it, then do not worry about it. In this article I will let you know how to do that.

First Attempt: Failed

Normally what we do is just press “CTRL + F”, enter ‘*’ in the ‘find’ field and click on “Find All”. It will show all the entries in the search result and there is no confusion in that. When we search for ‘*’ it searches for everything and this is not what we want.

wildcard characters in excel find with asterisk

Second Attempt: Failed

Now, let us try finding with the combination of ‘*’ and the text. Say, we are searching for ‘windows*’ and what could you see is, text starting with ‘windows’ in the search results. This is also expected when finding using wildcard characters and pretty clear that this is also not going to solve our problem.

wildcard characters in excel find with asterisk and text

Third Attempt: Success!

Problem in our first and second attempt is that, Excel is treating the asterisk character as wildcard character and giving the result based on that. But, we want to search wildcard character as the normal text.

So, we need to make Excel understand that, we want to search asterisk as the normal text and not as a wildcard character and it can be done. Yes, to make that happen we need to use the special character called Tilde (~). We can find this special character left to the ‘1’ keypad on your keyboard. This is where I could see on my keyboard and it may be at different place on your keyboard based on the location where you are.

wildcard characters in excel find with asterisk and tilde

Now, when you enter ‘windows~*’ in the find field and see the results, then you could see the results containing the asterisk. This means that we could find the asterisk as the normal text character and we are successful!

So, when we want to search any wildcard character as the normal text, then we need to use Tilde (~) special character along with the wildcard character in the ‘find’ field.

This is the way to search wildcard characters in Excel as the normal text and this is one of the easy ways to do that.

If you have anything to add or any other easy ways, then do share with us through comments.

Posted by on , in Category Office with Tags
Sridhar is a Software Engineer who loves to stay updated with developments in the world of technology. He is fond of writing everything related to Internet, Computers and Mobile and Desktop Operating Systems.