Hello friends its Sachin here with new blog on Wildcard character in excel. In this blog we are the going to learn the following:

1.) What are Wildcard character

2.) How can we use them with examples

3.) Excel function that can handle Wildcard characters.

What are Wildcard characters

Wildcards are special characters that can take any place of any character (hence the name – wildcard). 

There are three wildcard characters in Excel:

  1. * (asterisk) – It represents any number of characters. For example, Ex* could mean Excel, Excels, Example, Expert, etc.
  2. ? (question mark) – It represents one single character. For example, Tr?mp could mean Trump or Tramp.
  3. ~ (tilde) – It is used to identify a wildcard character (~, *, ?) in the text. For example, let’s say you want to find the exact phrase Excel* in a list. If you use Excel* as the search string, it would give you any word that has Excel at the beginning followed by any number of characters (such as Excel, Excels, Excellent). To specifically look for excel*, we need to use ~. So our search string would be excel~*. Here, the presence of ~ ensures that excel reads the following character as is, and not as a wildcard.


How can we use them with example

A* – Represents anything which starts with “A”
For Example – Apple, Anmol, Amul, Anna Hajare etc
*A– Represents anything which ends with “A”
For example – Khazana, Bhavya etc
*A*– Represents anything that start with anything and also end with
anything but have “A” somewhere in between
For example – Sachin, Pawan , Yash etc
A???– Represents anything which starts with A and then contain 3 other
Words
For example – Anar, Anna, Amir etc
??N– Represents anything which have 2 letters in beginning and “A” at
the end
For example – Pen , Pin etc

Example 1 – Filtering Mobile number that ends with 86 ====> *86

Explanation – For finding the mobile numbers that end with 86 we used *86 in filter search and filtered it out. 🙂

Example -2 Lets suppose you know somebody but only by surname or first name and want to know his/her name

You got two ways

  1. Use partial V Lookup with *
  2. Use find function

here we have used partial V lookup

Explanation – here in lookup value to join * and Surname we can use (&) Symbol and then construct the formula of V lookup exact match.

Excel function that can handle Wildcard characters.

Function
Average If Returns the average (arithmetic mean) of all the cells in a range that meet one criteria.
Average Ifs Returns the average of all cells that meet multiple criteria.
Count IfCounts the number of cells that meet one criteria.
Count ifsCounts the number of cells that meet multiple criteria
H LookupLooks in the top row of an array and returns the value of
the indicated cell.
MatchLooks up values in a reference or array and returns its
relative position as an integer.
Search Finds one text value within another (not case-sensitive).
Sum IfAdds the cells in one or more rows or columns specified
by multiple criteria.
Sum IfsAdds the cells in one or more rows or columns specified
by multiple criteria.
V LookupLooks in the first column of an array and moves across
the row to return the value of a cell.

Once again Thank You for reading the blog

in case of any query or suggestion feel free to comment.

31 thoughts on “Wildcard Character – In Excel and Their Best use

Leave a Reply

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