“Call Tilde” to tame Excel Wildcards – Find and Replace Wildcard Characters [Quick Tip]

 

Wildcards make finding and/or replacing text or numbers very easy. It just adds more versatility to bone dry, unintelligent search function of any program.

But this added flexibility sometimes gets in the way. For example you have a text written as follows:

Pak*Accountants

Pak Accountants

Hasaan Fazal

Hasaan*Fazal

Now both are different we can see. And asterisk is there in the first one by mistake and also in the last one. So I want to search for it and correct it.

Try 1: Fail!

The usual steps will be to hit Ctrl+F and in the search bar just mention asterisk (*) sign and hit find all. Well guess what, search results will pull all the entries as “results found”. Everything will be in the found results. Total mess!

Try 2: Fail!

Another possibility we can think of to find is to make a combo of asterisk and text to find the mistakes like:

Pak*

But even this is going to fail. Reason is that just like in the first try asterisk was working as a wildcard, now it is going to pull all the results that start with “Pak”. So we are going no where and we can definitely not use it to correct the problem.

Try 3: SUCCESS! [Try Try Try]

The reason of this fuss is that Excel is treating asterisk as a wildcard where as you are trying to give it as a text character. So how to make Excel understand that you want to revoke the position of asterisk as a wildcard and search it as a normal text character. Well for that you have to call in a special character! “Code name” Tilde ! (~). Yes it is the same sign at the left of 1 key on the keyboard. Well in my case it is at the left. Look at your keyboard closely if you are using a different keyboard layout as it might be different depending on geographical settings.

So, if you make your search argument like this:

Pak~*

then excel will search search treating asterisk as a normal text character.

So now you now how to make even the wild ones more civilized!