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

Wildcards successful Microsoft Excel fto you hunt for partial matches, broaden your filters, and make formulas that notation cells containing definite strings. They correspond non-specified characters to assistance you find substance values with "fuzzy" matches.

The Wildcards: Asterisk (*) and Question Mark (?)

There are 2 wildcards successful Excel, and knowing their intent is important to knowing however wildcards enactment overall.

The Asterisk Wildcard: Any Number of Characters

The archetypal of the 2 wildcards successful Microsoft Excel is an asterisk, which represents immoderate fig of characters, including nary characters.

For example:

• *OK* matches with immoderate cells that incorporate "OK," with immoderate fig of characters earlier oregon aft (including nary characters).
• OK* matches with immoderate cells that commencement with "OK," with immoderate fig of characters (including nary characters) after, but thing before.
• *OK matches with immoderate cells that extremity with "OK," with immoderate fig of characters (including nary characters) before.

The Question Mark Wildcard: Any Single Character

Microsoft Excel's 2nd wildcard is the question mark, which substitutes immoderate azygous character.

For example:

• ?OK? matches with immoderate cells that incorporate a azygous quality earlier "OK," and a azygous quality after.
• OK? matches with immoderate cells that incorporate a azygous quality aft "OK", but thing before.
• ?OK matches with immoderate cells that incorporate a azygous quality earlier "OK", but thing after.

Question Mark and Asterisk Wildcards Combined

You tin besides usage the question people and asterisk unneurotic to find results that person finite numbers of characters successful immoderate positions, but immoderate fig of characters successful others.

For example,

• ??OK* matches with immoderate cells that statesman with 2 characters, past incorporate "OK," with immoderate fig of characters astatine the extremity (including none).
• *OK? matches with immoderate cells that statesman with immoderate fig of characters (including none), past incorporate "OK," and extremity with 1 much character.
• ?OK* matches with immoderate cells that statesman with a azygous character, past incorporate "OK," and person immoderate fig of characters (including none) thereafter.

Canceling Wildcards: Tilde (~)

Sometimes, you mightiness really privation to hunt for question marks and asterisks arsenic characters successful their ain close successful your Excel worksheet. This is wherever the 3rd wildcard character—the tilde—comes into play: simply spot it earlier the question people oregon asterisk to archer Excel you don't privation them to beryllium treated arsenic wildcards.

For example:

• *~? matches with immoderate cells that incorporate immoderate fig of characters (including none) astatine the start, and a question people astatine the end.
• *~?* matches with immoderate cells that incorporate immoderate fig of characters (including none) connected either broadside of a question mark.
• *~*? matches with immoderate cells that commencement with immoderate fig of characters, followed by an asterisk, followed by a azygous character.

Using Wildcards successful Searches

One of the astir communal uses of wildcards successful Microsoft Excel is to find characters successful a workbook and, if needed, regenerate them with alternatives.

Find Character Strings

In this database of merchandise codes, the letters astatine the opening correspond wherever the merchandise was manufactured (AUS for Australia, UK for the United Kingdom, USA for the United States, and CAN for Canada), the archetypal digit represents the applicable store section (1 is clothing, 2 is homeware, 3 is sports, and 4 is garden), and codes with a missive astatine the extremity are nationalist merchantability lone (A) oregon planetary merchantability lone (B).

Let's accidental you privation to find each the homeware products that are for nationalist merchantability only.

Press Ctrl+F to motorboat the Find tab of the Find And Replace dialog box, and successful the Find What field, type:

*2??A

where

• The asterisk and the fig 2 archer Excel to hunt for cells that commencement with immoderate fig of characters, followed by the fig 2. You request to usage an asterisk here, since immoderate countries are represented by 2 letters portion others are represented by three.
• The 2 question marks followed by the missive A archer Excel that the remainder of the drawstring indispensable beryllium made up of 2 characters and the missive A.

Then, erstwhile you click "Find All," you'll spot a database astatine the bottommost of the Find And Replace dialog container that shows immoderate results that lucifer these criteria.

Now, let's accidental your purpose is to instrumentality the codes for each covering products that aren't restricted to nationalist oregon planetary income lone (in different words, codes that incorporate a state and a three-digit fig starting with 1, but don't extremity with a letter).

In the Find What field, type:

*1??

However, erstwhile you click "Find All," a merchandise ending successful a missive shows up successful the result, suggesting there's thing missing from the criteria.

In fact, this happened due to the fact that part of the erroneous codification contains the substring you entered for the query. So, to exclude these and instrumentality lone the codes that fully lucifer your query, click "Options," cheque "Match Entire Cell Contents," and click "Find All" again.

This time, lone the desired results are displayed.

Find And Replace Character Strings

This Excel database shows respondents' all-time favourite shot players. However, Maradona's sanction has been spelled successful 3 antithetic ways, and you privation to amend the incorrect ones.

First, property Ctrl+H to motorboat the Replace tab of the Find And Replace dialog box. Then, successful the Find What field, type:

Mar*

since each incorrect spellings commencement with these 3 letters, and it's the lone sanction successful the database that starts with this quality string.

Then, successful the Replace With, benignant the close spelling:

Maradona

and click "Replace All" to corroborate the corrections.

Now, each cases of this player's sanction are spelled correctly and consistently.

Using Wildcards successful Formulas

As good arsenic utilizing wildcards successful Excel's Find And Replace dialog box, you tin usage them successful arguments for assorted functions, specified arsenic XLOOKUP to make a partial lucifer lookup, COUNTIFS to number the fig of cells that lucifer aggregate criteria, and galore more.

In this example, you privation to usage the SUMIF relation to enactment retired the full terms of products originating successful the UK.

One mode to bash this would beryllium to type:

=SUMIF(A2:A18,"UK*",B2:B18)

into a blank cell, where

• SUMIF adds values unneurotic based connected acceptable criteria,
• A2:A18 is the scope containing the values to beryllium tested,
• "UK*" tells Excel to find immoderate cells successful A2 to A18 starting with "UK," followed by immoderate fig of characters, and
• B2:B18 is the array to sum.

However, accidental you past privation to find the full terms of products from different country. In the illustration above, you would request to alteration the look manually, a method that takes clip and is much apt to effect successful error.

Instead, benignant the criteria (in this case, "UK") successful a abstracted cell. Then, notation that compartment successful the formula, utilizing the ampersand (&) awesome to abstracted the compartment notation from the wildcard embedded successful quotation marks:

=SUMIF(A2:A18,D2&"*",B2:B18)

Now, erstwhile you alteration the state code, the full updates.

Using Wildcards successful Filters

Another usage of wildcards successful Microsoft Excel is successful filters.

Whether you're moving successful a formatted Excel array oregon an unformatted range, prime immoderate compartment successful the array, and property Ctrl+Shift+L to adhd filter buttons.

Now, let's accidental you privation to filter the merchandise codes to see lone those that incorporate the missive A astatine the end.

To bash this, click the Product_Code filter button, and successful the Search field, type:

*A

to see each codes ending successful the missive A. Since the asterisk wildcard is placed earlier the missive A successful the search, it doesn't prime up immoderate merchandise codes containing A astatine the commencement (such arsenic AUS458) oregon successful the mediate (such arsenic USA320B).

Once you've previewed the results successful the filter pop-up window, click "OK," and spot your information refined to lucifer your criteria.

Points to Note

Before you spell up and marque usage of wildcards successful your Excel spreadsheet, instrumentality a infinitesimal to work these last pointers:

• When utilizing wildcards successful formulas, you'll deed a hurdle if you effort to find a digit successful a scope of numbers. This is due to the fact that wildcards automatically person numbers to text. However, searching for digits successful a scope of numbers utilizing Excel's Find And Replace instrumentality oregon filters volition enactment conscionable fine, arsenic volition uncovering numbers successful cells that besides incorporate substance values.
• Although galore functions enactment the usage of wildcards, some—including the IF function—don't.
• Adding wildcards to formulas makes them much complicated, perchance expanding the hazard of errors if you don't instrumentality other care. Always retrieve to embed wildcards wrong quotation marks successful formulas!

---

As good arsenic utilizing wildcards to broaden your hunt capabilities successful Excel, you tin besides usage them to find and regenerate substance successful Microsoft Word. The large quality betwixt the 2 is that, alongside the asterisk and question mark, Word besides lets you usage quadrate brackets ([ ]) arsenic wildcards to lucifer much than 1 item.