Save an hour of work a day with these 5 advanced Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.

  • How to create beautiful table formatting instantly…
  • Why to rethink the way you do VLOOKUPs
  • Plus, we’ll reveal why you shouldn’t use PivotTables and what to use instead…

Using Excel’s wildcard character

At times, you may want to use certain string matching or search functions — like SEARCH — without knowing exactly what you’re looking for. For example, you may want to search for a state that begins with the word “New” — but match against all possible outcomes, including “New Hampshire”, “New York”, and “New Jersey”. To do this, you can use something called a “wildcard” character to tell Excel that you’re looking for something, but you’re not exactly sure what yet.

Think of Excel’s wildcard characters like the jokers in a deck of cards. They can take on any value they want to.

Here’s some more information on how wildcards work:

The basic wildcard structure

There are two key types of wildcards in Excel. Here’s what they look like and how you use them.

WildcardNameExplanation
?Question markTakes the place of a single character. For example, “Tr?y” matches with “Tray”, “Troy”, and “Trey”, but not “Trolley”
*AsteriskCan take the place of any number of characters. For example, “Tr*y” matches with “Tray”, “Troy”, and “Trolley”.
~TildaTells Excel that the following character should be treated as a normal character and not a wildcard. For example, “Tr~?y” matches only with “Tr?y”, not “Tray” or “Troy”.

Per the chart above, use the question mark (?) when you want to accept only a single character, and the asterisk (*) when you want to accept multiple characters.

Here are a few examples of wildcards in action with the SEARCH function:

=SEARCH("c?d", "Abracadabra")
Output: 5

The above returns the number 5, because “c?d” matches the phrase “cad”, which is found five characters in to the given string.

=SEARCH("c*b", "Abracadabra")
Output: 5

The above returns the number 5, because “c*b” matches the phrase “cadab”, which is found five characters in to the given string.

=SEARCH("c?b", "Abracadabra")
Output: #VALUE!

The above returns a #VALUE! error, because “c?b” does not match any phrase within the given string. There is no instance in which a “c” and a “b” are only separated by one letter.

When can I use wildcards?

You can use wildcards in any situation in which you are matching one string against another. There are many such uses, but here are some of the most common:

  • When searching for a string using the SEARCH or FIND functions ;
  • When performing a VLOOKUP or HLOOKUP ; and
  • When using INDEX MATCH .

Wildcards can be extremely helpful if you want to match or search against a partial phrase rather than an entire string.

Save an hour of work a day with these 5 advanced Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.

  • How to create beautiful table formatting instantly…
  • Why to rethink the way you do VLOOKUPs
  • Plus, we’ll reveal why you shouldn’t use PivotTables and what to use instead…

Comments

  • Getting started
  • Excel function basics
  • Mathematical functions
  • Dates and times
  • String functions
    • Excel string functions: An introduction
    • Combine text in Excel using CONCATENATE
    • The TRIM function in Excel
    • Using the LEN function
    • How to count characters in Excel
    • How to search Text in Excel
    • Using Excel’s wildcard character
    • Using Excel’s RIGHT function
    • Using Excel’s LEFT formula
    • Using Excel’s MID function
    • How to use Excel’s VALUE function
  • Lookup functions
  • Logical functions
  • Charts and graphs
  • PivotTables
  • Advanced formatting
  • Reference
  • Blog

Save an hour of work a day with these 5 Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.