How You Can Use Wildcards in Microsoft Excel to Refine Your Search

Excel wildcards: a powerful tool for efficient search and filtering

This article will dive into the power of wildcards in Microsoft Excel, including their application in search, formulas, and filters, and some details to note. Wildcards allow you to perform fuzzy matching, making it more flexible to find and process data.

* Wildcards: asterisks ( ) and question marks (?)**

Excel mainly uses two wildcard characters: asterisk (*) and question mark (?).

• * Asterisk ( ): Any number of characters** The asterisk represents any number of characters, including zero characters.

  For example:

  • *OK* matches cells containing "OK", which can have any number of characters (including zeros) before and after "OK".
  • OK* matches cells starting with "OK", and "OK" can have any number of characters (including zeros).
  • *OK matches cells ending with "OK", and "OK" can be preceded by any number of characters (including zero).

Value to be tested	Conditions: *OK*	Conditions: OK*	Conditions: *OK
OK	match	match	match
Oklahoma	match	match	Missing
Shook	match	Missing	match
Look	match	Missing	Missing
Looking	match	Missing	Missing

• Question mark (?): any single character The question mark represents any single character.

  For example:

  • ?OK? Matches a cell containing "OK", with one character before and after "OK".
  • OK? Match a cell that starts with "OK" and is followed by a character.
  • ?OK matches a cell that starts with a character followed by "OK".

Value to be tested	Conditions:?OK?	Conditions: OK*	Conditions:?OK
Joke	match	Missing	Missing
Oka	Missing	match	Missing
Wok	Missing	Missing	match

• Combination of asterisks and question marks You can combine asterisks and question marks to achieve a more accurate match.

  For example:

  • ??OK* match starts with two characters, then "OK", followed by any number of characters.
  • *OK? Matches a cell containing "OK" followed by a character.
  • ?OK* matches start with a character, then "OK", followed by any number of characters.

Value to be tested	Conditions:??OK*	Conditions: *OK?	Conditions:?OK*
Took	match	Missing	Missing
Books	match	match	Missing
Spooky	Missing	match	Missing
Poky	Missing	match	match
Jokes	Missing	Missing	match

Cancel wildcard: tilde (~)

If you need to search for a question mark or asterisk itself, instead of using it as a wildcard, you need to prefix the tilde (~).

For example:

• ~*? Match cells that start with any number of characters and end with a question mark.
• ~*?* matches the cell containing the question mark.
• *~*? Matches a cell that starts with any number of characters, followed by an asterisk, and then a character.

Value to be tested	Conditions: *~?	Conditions: *~?*	Conditions: *~*?
Dinner?	match	match	Missing
???k	Missing	match	Missing
D?*y	Missing	match	match

Application of wildcard characters in search

One of the most common uses of wildcard characters is to find and replace characters in Excel.

Application of wildcard characters in formulas

Wildcards can also be used in various functions' parameters, such as SUMIF , COUNTIFS , XLOOKUP , etc.

Application of wildcard characters in filtering

Wildcards are also suitable for filtering operations.

Things to note

• When using wildcards in formulas, you may encounter problems if you try to find numbers in the range of numbers, because wildcards automatically convert numbers to text.
• Not all functions support wildcards.
• Using wildcards in formulas can make the formula more complicated and increase the risk of errors. Be sure to enclose wildcards in quotes!

In addition to using wildcards in Excel, you can also use them in Microsoft Word to find and replace text. In addition to asterisks and question marks, square brackets ([ ]) can also be used as wildcards to match multiple items.

The above is the detailed content of How You Can Use Wildcards in Microsoft Excel to Refine Your Search. For more information, please follow other related articles on the PHP Chinese website!