Excel Tip: Excel Wildcard Characters

Are you looking for certain items that share last 3 characters? Are you not certain that the name you are searching for is Anna or Anne? Or are you looking for licence plates starting with C ? Than it is time to learn about Excel Wildcard Characters.

Excel has a couple of wildcard characters. Though they may not seem to of any significance at first sight, you will realize their value when you start to use them in formulas. Here are excel wildcard characters:

? (Question Mark) : ? helps you look for any single character. For example, if you search dictionary for d?rk, excel will find “dark” and “dirk”.

Following formulas will sum all the items corresponding to dirk, dark,derk,etc.

=SUMIF(A2:A101;”d?rk”;B2;B101)

* (Asteriks) : * helps you look for any number of characters. For example, if you search ice cream list for daily*ice cream, excel will find you “daily vanilla icecream”, “daily strawberry ice cream”, “daily chocolate icecream”, an so on…

Following formula will sum all the items corresponding with Week10, Week11, Week12, etc.

=SUMIF(A2:A101;”Week1*”;B2;B101)

~ (Tilde) : helps you get exact mach for your search with wildcards. For example, while “jo* smith” finds you “john smith” , “john smith jr” and “john smith 3rd” ; “jo~* smith” will find you “john smith”.

Following formula will sum all the items corresponding with “john smith”.

=SUMIF(A2:A101;“jo~* smith”;B2;B101)

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

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