Analyze and Simplify: Wildcard Usage

Ever wondered if Microsoft Excel supports wildcards too? Yes, it does support *, ?, and ~.

I found this specifically useful when I had to remove certain string of characters from each row.

There were more than 1000 records of file name including the path from which they were derived.

Luckily, there was a fragment 640/, common for all the records.

Every record began with a rampant series of numbers and characters, not at all a pleasing sight.

But 640/ was a common thing.

Quite interesting analysis was done by everyone in the team, including but not limited to usage of poor VLOOKUP, delimiter and several other formulas.

Eventually I figured out that a simple Find and Replace should work here as there was this repetitive savior fragment before the beginning of the actual file name or the part of interest.

So, imagine a row of garbage/junk data holding the jewel/file name under the protection provided by the savior/640/. For example, ccabg/jhdauif/hjjj/spec//cc_f640/FILE_NAME. You have more than 1000 such records with the content and length of the junk data varying for each one of them.

Goal: To extract the file name from the junk data/eliminate the junk data

All you have got to do in such cases is press Ctrl+H and type “*640/” under Find and Replace it with nothing and press Replace All. Assuming you selected the entire column of interest, the entire column is now holding the File Name which you were interested in. This simple one and a half seconds activity can impress those who do not know that wildcards are supported in Excel.

So, next time, remember to use the below mentioned table in crisis:

Wildcard	Usage
? (Question Mark)	Replaces any single character. Eg. N?me will search Name
* (Asterisk)	Replaces string of characters. Eg. As explained above
~ (Tilde) followed by ?, *, or ~	To find ? or * or ~ in the data

Hence, the tilde should be used if you are looking to find ? or ~ or * within the data and do not intend to use them as wildcards.

This was indeed a short lesson but when applied at the right time can result in immense amount of time and effort saving.

Always remember that your goal should be to simplify the task in hand, and for that you need to analyze the data in hand, don’t just start with formulas and macro that you would have learnt from some amazing online tutorials or any other source. Hence, emphasize on analyzing the data and come up with simple solutions for complex problems. That's what learning is all about!

Happy Learning! J