pivotronics

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:

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

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:

Now the results look as desired as below:

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.. 🙂