Finding and replacing required text and numbers is very easy with the use of Wildcard Characters in Microsoft Excel. It might be a single occurrence or multiple occurrences – everything can be found and replaced easily using Wildcards.
How to use Wildcards in Excel to Find and Replace
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
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.
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.
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.
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.
Read: How to make Excel cells expand to fit Text automatically
If you have anything to add or any other easy ways, then do share with us through comments.