I learned a new trick last week – using wildcards in Excel formulas. I’ve always known you can use wildcards characters such as the asterisk * or ? when searching for items in windows explorer, but I wasn’t quite sure how to use them in Excel formulas.
For example, many times I want to calculate a formula if a certain condition is present, and usually using a =IF(condition..) formula works just fine when looking at numbers. It can become more complicated if you are looking at text and want to make a calculation based upon it or other criteria.
COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. COUNTIF uses a syntax of range (the range of cells to count) and criteria (controls which cells should be counted). Non-numeric or text criteria
However you use the formula, it can help summarize long columns of data containing mixed text or numbers quite easily.
Let’s say we have a column in our spreadsheet of part descriptions or telephone numbers. We want to count how many phone numbers we have in area code 480 or 602. In a new column, use the formula below to get a total number of items starting with area code (480).
=COUNTIF([@Phone Number],{“480*”})>0
This formula will look at a column with a heading of Phone Number and returns a 1 or 0 (TRUE/FALSE) if the item starts with a 480. You do need the curly brackets {} and text needs to be within double quote marks “text”. If you want to find phone numbers that end with 480, you would place the wildcards * at the beginning like {“*480”}. If you want columns that CONTAIN a term, you start and end the text with wildcards, such as {“*480*”}.
If you want more than one item you can use the syntax {“a*”