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:
- * (asterisk) – It represents any number of characters. For example, Ex* could mean Excel, Excels, Example, Expert, etc.
- ? (question mark) – It represents one single character. For example, Tr?mp could mean Trump or Tramp.
- ~ (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 |
For example – Anar, Anna, Amir etc
|??N||– Represents anything which have 2 letters in beginning and “A” at|
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
- Use partial V Lookup with *
- 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.
|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 If||Counts the number of cells that meet one criteria.|
|Count ifs||Counts the number of cells that meet multiple criteria|
|H Lookup||Looks in the top row of an array and returns the value of|
the indicated cell.
|Match||Looks 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 If||Adds the cells in one or more rows or columns specified|
by multiple criteria.
|Sum Ifs||Adds the cells in one or more rows or columns specified|
by multiple criteria.
|V Lookup||Looks 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.