Microsoft Excel behaves strange when tried to replace or remove asterisk (*). Why..?


Here I came across a scenario of Microsoft Excel behaving strange, when trying to find and replace asterisk character (*) with another character.

Scenario: Here is a list of data in an excel worksheet containing some fictitious data of names and salaries of a few employees. You can see that some of the names are containing an asterisk character (*) in the mid. These are entered inadvertently. Now the requirement is to cleanse the data by removing these asterisk characters from the “Name” column. I used the excel keyboard shortcut “CNTRL + H” to invoke the “Find and Replace” dialogue. since i want to remove these asterisk characters, i replace the * with nothing (i.e. entered nothing in the “Replace with” text box of the “Find and Replace” dialogue) as in the below picture:

Find and Replace wildcard character in Excel 2007
Find and Replace wildcard character in Excel 2007

Problem: Interestingly it knocked off all the data from the worksheet as in the below picture:

Find and Replace wildcard character in Excel 2007
Find and Replace wildcard character in Excel 2007

Justification: It appears like Excel going mad. But the fact is that Excel is simply doing the job that it is asked to silently. The asterisk (*) character is actually the wildcard character representing zero or more characters. Hence when Excel sees asterisk in the Find What text box, it just looks for zero or more characters, said another way, all the characters. Then replaces all the characters with nothing, said another way, knocks off all the data.

Then how do we achieve this task of removing only the asterisk character from the data.?

Solution: Prefix the asterisk (*) character in the “Find What” text box with tilde (~) operator as below:

Find and Replace wildcard character in Excel 2007
Find and Replace wildcard character in Excel 2007

Now the results look as desired as below:

Find and Replace wildcard character in Excel 2007 using tild operator
Find and Replace wildcard character in Excel 2007 using tilde operator

Conclusion: The tilde operator acts like the escape characters and escapes the inherent meaning of  asterisk (*) from the knowledge of Excel, letting Excel treat asterisk as just another character.

Hope you enjoyed reading this post. Please do share your comments or suggestions or questions, if any..

Following this blog is now just a step away – see on the top right.

Happy time until my next blog post.. 🙂

One thought on “Microsoft Excel behaves strange when tried to replace or remove asterisk (*). Why..?

Add yours

Leave a Reply to Arif Irawan Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

Up ↑