Wildcards excel это что
When you are looking for something but not exactly sure exactly what, wildcards are a perfect solution. You can think of a wildcard as a joker that can take on any value. There are only 3 wildcard characters in Excel (asterisk, question mark, and tilde), but they can do so many useful things!
Excel wildcard characters
In Microsoft Excel, a wildcard is a special kind of character that can substitute any other character. In other words, when you do not know an exact character, you can use a wildcard in that place.
The two common wildcard characters that Excel recognizes are an asterisk (*) and a question mark (?). A tilde (~) forces Excel to treat theses as regular characters, not wildcards.
Wildcards come handy in any situation when you need a partial match. You can use them as comparison criteria for filtering data, to find entries that have some common part, or to perform fuzzy matching in formulas.
Asterisk as a wildcard
The asterisk (*) is the most general wildcard character that can represent any number of characters. For example:
- ch* - matches any word that begins with "ch" such as Charles, check, chess, etc.
- *ch - substitutes any text string that ends with "ch" such as March, inch, fetch, etc.
- *ch* - represents any word that contains "ch" in any position such as Chad, headache, arch, etc.
Question mark as a wildcard
The question mark (?) represents any single character. It can help you get more specific when searching for a partial match. For example:
- ? - matches any entry containing one character, e.g. "a", "1", "-", etc.
- ?? - substitutes any two characters, e.g. "ab", "11", "a*", etc.
- . -. - represents any string containing 2 groups of 3 characters separated with a hyphen such as ABC-DEF, ABC-123, 111-222, etc.
- pri?e - matches price, pride, prize, and the like.
Tilde as a wildcard nullifier
The tilde (~) placed before a wildcard character cancels the effect of a wildcard and turns it into a literal asterisk (~*), a literal question mark (~?), or a literal tilde (~~). For example:
- *~? - finds any entry ending with question mark, e.g. What?, Anybody there?, etc.
- *~** - finds any data containing an asterisk, e.g. *1, *11*, 1-Mar-2020*, etc. In this case, the 1 st and 3 rd asterisks are wildcards, while the second one denotes a literal asterisk character.
Find and replace wildcards in Excel
The uses of wildcard characters with Excel's Find and Replace feature are quite versatile. The following examples will discuss a few common scenarios and warn you about a couple of caveats.
How to search with wildcard
By default, the Find and Replace dialog is configured to look for the specified criteria anywhere in a cell, not to match the entire cell contents. For example, if you use "AA" as your search criteria, Excel will return all the entries containing it such as AA-01, 01-AA, 01-AA-02, and so on. That works great in most situations, but under certain circumstances can be a complication.
In the below dataset, supposing you want to find the IDs that consist of 4 characters separated with a hyphen. So, you open the Find and Replace dialog (Ctrl + F) , type ??-?? in the Find what box, and press Find All. The result looks a bit perplexing, isn't it?
Technically, strings like AAB-01 or BB-002 also match the criteria because they do contain a ??-?? substring. To exclude these from the results, click the Options button, and check the Match entire cell contents box. Now, Excel will limit the results to only the ??-?? strings:
How to replace with wildcard
In case your data contains some fuzzy matches, wildcards can help you quickly locate and unify them.
In the screenshot below, you can see two spelling variations of the same city Homel and Gomel. We'd like to replace both with another version - Homyel. (And yes, all three spellings of my native city are correct and generally accepted :)
To replace partial matches, this is what you need to do:
- Press Ctrl + H to open the Replace tab of the Find and Replace dialog.
- In the Find what box, type the wildcard expression: ?omel
- In the Replace with box, type the replacement text: Homyel
- Click the Replace All button.
And observe the results:
How to find and replace wildcard characters
To find a character that Excel recognizes as a wildcard, i.e. a literal asterisk or question mark, include a tilde (~) in your search criteria. For example, to find all the entries containing asterisks, type ~* in the Find what box:
If you'd like to replace the asterisks with something else, switch to the Replace tab and type the character of interest in the Replace with box. To remove all the found asterisk characters, leave the Replace with box empty, and click Replace all.
Filter data with wildcards in Excel
Excel wildcards also come very useful when you have a huge column of data and wish to filter that data based on condition.
In our sample data set, supposing you want to filter the IDs beginning with "B". For this, do the following:
-
to the header cells. The fastest way is to press the Ctrl + Shift + L shortcut.
- In the target column, click the filter drop-down arrow.
- In the Search box, type your criteria, B* in our case.
- Click OK.
This will instantly filter the data based on your wildcard criteria like show below:
Wildcards can also be used with Advanced Filter, which could make it a nice alternative to regular expressions (also called regexes by tech gurus) that Excel does not support. For more information, please see Excel Advanced Filter with wildcards.
Excel formulas with wildcard
First off, it should be noted that quite a limited number of Excel functions support wildcards. Here is a list of the most popular functions that do with formula examples:
AVERAGEIF with wildcards - finds the average (arithmetic mean) of the cells that meet the specified condition.
AVERAGEIFS - returns the average of the cells that meet multiple criteria. Like AVERAGEIF in the above example allows wildcards.
COUNTIF with wildcard characters - counts the number of cells based on one criterion.
COUNTIFS with wildcards - counts the number of cells based on multiple criteria.
SUMIF with wildcard- sums cells with condition.
SUMIFS - adds cells with multiple criteria. Like SUMIF in the above example accepts wildcard characters.
VLOOKUP with wildcards - performs a vertical lookup with partial match.
HLOOKUP with wildcard - does a horizontal lookup with partial match.
XLOOKUP with wildcard characters - performs a partial match lookup both in a column and a row.
MATCH formula with wildcards - finds a partial match and returns its relative position.
XMATCH with wildcards - a modern successor of the MATCH function that also supports wildcard matching.
SEARCH with wildcards - unlike the case-sensitive FIND function, case-insensitive SEARCH understands wildcard characters.
If you need to do partial matching with other functions that do not support wildcards, you will have to figure out a workaround like Excel IF wildcard formula.
The following examples demonstrate some general approaches to using wildcards in Excel formulas.
Excel COUNTIF wildcard formula
Let's say you wish to count the number of cells containing the text "AA" in the range A2:A12. There are three ways to accomplish this.
The easiest one is to include wildcard characters directly in the criteria argument:
In practice, such "hardcoding" is not the best solution. If the criteria changes at a later point, you'll have to edit your formula every time.
Instead of typing the criteria in the formula, you can input it in some cell, say E1, and concatenate the cell reference with the wildcard characters. Your complete formula would be:
Alternatively, you can input a wildcard string (*AA* in our example) in the criteria cell (E1) and include only the cell reference in the formula:
All three formulas will produce the same result, so which one to use is a matter of your personal preference.
Note. The wildcard search is not case sensitive, so the formula counts both upper case and lowercase characters like AA-01 and aa-01.
Excel wildcard VLOOKUP formula
When you need to look for a value that does not have an exact match in the source data, you can use wildcard characters to find a partial match.
In this example, we are going to look up the IDs that start with specific characters, and return their prices from column B. To have it done, enter the unique parts of the targets IDs in cells D2, D3 and D4 and use this formula to get the results:
=VLOOKUP(D2&"*", $A$2:$B$12, 2, FALSE)
The above formula goes to E1, and due to the clever use of relative and absolute cells references it copies correctly to the below cells.
Note. As the Excel VLOOKUP function returns the first found match, you should be very careful when searching with wildcards. If your lookup value matches more than one value in the lookup range, you may get misleading results.
Excel wildcard for numbers
It is sometimes stated that wildcards in Excel only work for text values, not numbers. However, this is not exactly true. With the Find and Replace feature as well as Filter, wildcards work fine for both text and numbers.
Find and Replace with wildcard number
In the screenshot below, we are using *4* for the search criteria to search for the cells containing the digit 4, and Excel finds both text strings and numbers:
Filter with wildcard number
Likewise, Excel's auto-filter has no problem with filtering numbers containing "4":
Why Excel wildcard not working with numbers in formulas
Wildcards with numbers in formulas is a different story. Using wildcard characters together with numbers (no matter whether you surround the number with wildcards or concatenate a cell reference) converts a numeric value into a text string. As the result, Excel fails to recognize a string in a range of numbers.
For instance, both of the below formulas count the number of strings containing "4" perfectly well:
But neither can identify digit 4 within a number:
How to make wildcards work for numbers
The easiest solution is to convert numbers to text (for example, by using the Text to Columns feature) and then do a regular VLOOKUP, COUNTIF, MATCH, etc.
For instance, to get the count of cells that begin with the number in E1, the formula is:
In situation when this approach is not practically acceptable, you will have to work out your own formula for each specific case. Alas, a generic solution does not exist :( Below, you will find a couple of examples.
Example 1. Excel wildcard formula for numbers
This example shows how to count numbers that contain a specific digit. In the sample table below, suppose you want to calculate how many numbers in the range B2:B12 contain "4". Here's the formula to use:
How this formula works
Working from the inside out, here's what the formula does:
The ISNUMBER function takes it from there and changes any number to TRUE and error to FALSE:
A double unary operator (--) coerces TRUE and FALSE to 1 and 0, respectively:
Finally, the SUMPRODUCT function adds up the 1's and returns the count.
Note. When using a similar formula in your worksheets, in no case you should include "$" or any other currency symbol in the SEARCH function. Please remember that this is only a "visual" currency format applied to the cells, the underlying values are mere numbers.
Example 2. Wildcard formula for dates
The SUMPRODUCT formula discussed above works beautifully for numbers but will fail for dates. Why? Because internally Excel stores dates as serial numbers, and the formula would process those numbers, not the dates displayed in cells.
To overcome this obstacle, utilize the TEXT function to convert dates to text strings, and then feed the strings to the SEARCH function.
Depending on exactly what you aim to count, the text formats may vary.
To count all the dates in C2:C12 that have "4" in the day, month or year, use "mmddyyyy":
To count only the days that contain "4" ignoring months and years, use the "dd" text format:
That's how to use wildcards in Excel. I hope this information will prove useful in your work. Anyway, I thank you for reading and hope to see you on our blog next week!
В Excel всего 3 подстановочных символа (звездочка, вопросительный знак и тильда), и с их помощью можно многое сделать.
В этом уроке я покажу вам четыре примера, в которых эти подстановочные знаки Excel - настоящие спасители.
Подстановочные знаки Excel - Введение
Подстановочные знаки - это специальные символы, которые могут занимать любое место любого символа (отсюда и название - подстановочный знак).
В Excel есть три подстановочных символа:
- * (звездочка) - Он представляет собой любое количество символов. Например, Ex * может означать Excel, Excel, Пример, Эксперт и т. Д.
- ? (вопросительный знак) - Он представляет собой один-единственный персонаж. Например, Tr? Mp может означать Трампа или Бродяги.
- ~ (тильда) - Он используется для обозначения подстановочного знака (~, *,?) В тексте. Например, допустим, вы хотите найти точную фразу Excel * в списке. Если вы используете Excel * в качестве строки поиска, он даст вам любое слово, в начале которого есть Excel, за которым следует любое количество символов (например, Excel, Excels, Excellent). Чтобы специально искать excel *, нам нужно использовать ~. Таким образом, наша строка поиска будет выглядеть следующим образом: excel ~ *. Здесь наличие ~ гарантирует, что excel прочитает следующий символ как есть, а не как подстановочный знак.
Примечание: я не встречал много ситуаций, когда вам нужно было бы использовать ~. Тем не менее, это полезная функция.
Теперь давайте рассмотрим четыре замечательных примера, в которых подстановочные знаки выполняют всю тяжелую работу.
Подстановочные знаки Excel - примеры
Теперь давайте рассмотрим четыре практических примера, в которых подстановочные знаки Excel могут оказаться очень полезными:
- Фильтрация данных с использованием подстановочного знака.
- Частичный поиск с использованием подстановочного знака и ВПР.
- Найти и заменить частичные совпадения.
- Подсчитайте непустые ячейки, содержащие текст.
Подстановочные знаки Excel пригодятся, когда у вас есть огромные наборы данных и вы хотите отфильтровать данные на основе условия.
Предположим, у вас есть набор данных, как показано ниже:
Вы можете использовать подстановочный знак звездочки (*) в фильтре данных, чтобы получить список компаний, начинающихся с алфавита A.
Вот как это сделать:
Как это работает? - Когда вы добавляете звездочку (*) после A, Excel будет фильтровать все, что начинается с A. Это связано с тем, что звездочка (являющаяся подстановочным знаком Excel) может представлять любое количество символов.
Теперь, используя ту же методологию, вы можете использовать различные критерии для фильтрации результатов.
Если вы используете А? С вместо этого в результате вы получите только ABC Ltd (поскольку между «a» и «c» допускается только один символ)
Частичный поиск необходим, когда вам нужно найти значение в списке, а точное совпадение отсутствует.
Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите найти компанию ABC в списке, но в списке есть ABC Ltd вместо ABC.
В этом случае нельзя использовать обычную функцию ВПР, так как значение поиска не имеет точного соответствия.
Если вы используете VLOOKUP с приблизительным соответствием, это даст вам неправильные результаты.
Однако вы можете использовать подстановочный знак в функции ВПР для получения правильных результатов:
Введите следующую формулу в ячейку D2 и перетащите ее для других ячеек:
Как работает эта формула?
В приведенной выше формуле вместо использования искомого значения как есть, оно окружено с обеих сторон символом подстановки Excel звездочкой (*) - «*» & C2 & »*»
Это говорит Excel, что ему нужно искать любой текст, содержащий слово в C2. Он может содержать любое количество символов до или после текста в C2.
Следовательно, формула ищет совпадение, и как только оно находит совпадение, она возвращает это значение.
3. Найдите и замените частичные совпадения
Подстановочные знаки Excel довольно универсальны.
Вы можете использовать его в сложных формулах, а также в основных функциях, таких как «Найти и заменить».
Предположим, у вас есть данные, как показано ниже:
В приведенных выше данных регион введен разными способами (например, Северо-Запад, Северо-Запад, Северо-Запад).
Это часто бывает с данными о продажах.
Чтобы очистить эти данные и сделать их согласованными, мы можем использовать «Найти и заменить» с помощью подстановочных знаков Excel.
Вот как это сделать:
- Выберите данные, в которых вы хотите найти и заменить текст.
- На главную -> Найти и выбрать -> Перейти. Откроется диалоговое окно «Найти и заменить». (Вы также можете использовать сочетание клавиш - Control + H).
- Введите следующий текст в диалоговом окне поиска и замены:
- Найти то, что: Север * З *
- Заменить: Северо-Запад
Это мгновенно изменит все различные форматы и сделает их совместимыми с Северо-Западом.
Как это работает?
В поле «Найти» мы использовали Север * З * который найдет любой текст, в котором есть слово North и где после него стоит буква «W».
Следовательно, он охватывает все сценарии (Северо-Запад, Северо-Запад и Северо-Запад).
Функция «Найти и заменить» находит все эти экземпляры, меняет их на северо-запад и делает согласованными.
4. Подсчитайте непустые ячейки, содержащие текст.
Я знаю, что вы умны и думаете, что в Excel уже есть встроенная функция для этого.
Ты абсолютно прав!!
Это можно сделать с помощью функции СЧЁТ.
НО… С этим есть одна небольшая проблема.
Часто, когда вы импортируете данные или используете лист других людей, вы замечаете, что есть пустые ячейки, хотя это может быть не так.
Эти ячейки выглядят пустыми, но в них стоит знак = ””. Проблема в том, что
Проблема в том, что функция COUNTA не считает это пустой ячейкой (она считает ее текстом).
В приведенном выше примере я использую функцию COUNTA для поиска непустых ячеек, и она возвращает 11, а не 10 (но вы можете четко видеть, что только 10 ячеек содержат текст).
Причина, как я уже упоминал, в том, что он не считает A11 пустым (хотя должен).
Но так работает Excel.
Исправление - использовать в формуле подстановочный знак Excel.
Ниже приведена формула, использующая функцию СЧЁТЕСЛИ, которая подсчитывает только те ячейки, в которых есть текст:
Эта формула сообщает Excel, что нужно считать, только если в ячейке есть хотя бы один символ.
в ?* комбо:
- ? (знак вопроса) означает наличие хотя бы одного символа.
- * (звездочка) освобождает место для любого количества дополнительных символов.
Примечание: Приведенная выше формула работает, когда в ячейках есть только текстовые значения. Если у вас есть список, содержащий как текст, так и числа, используйте следующую формулу:
Точно так же вы можете использовать подстановочные знаки во многих других функциях Excel, таких как IF (), SUMIF (), AVERAGEIF () и MATCH ().
Также интересно отметить, что, хотя вы можете использовать подстановочные знаки в функции ПОИСК, вы не можете использовать их в функции НАЙТИ.
Надеюсь, эти примеры дадут вам представление об универсальности и силе подстановочных знаков Excel.
Если у вас есть какой-либо другой инновационный способ его использования, поделитесь им со мной в разделе комментариев.
To locate a specific item when you can't remember exactly how it is spelled, try using a wildcard character in a query.
Wildcards are special characters that can stand in for unknown characters in a text value and are handy for locating multiple items with similar, but not identical data. Wildcards can also help with getting data based on a specified pattern match. For example, finding everyone named John on Park Street.
For more information about queries, see introduction to queries.
Here are some examples of wildcard characters for Access queries:
Description
Matches any number of characters. You can use the asterisk (*) anywhere in a character string.
wh* finds what, white, and why, but not awhile or watch.
Matches a single alphabet in a specific position.
b?ll finds ball, bell, and bill.
Matches characters within the brackets.
b[ae]ll finds ball and bell, but not bill.
Excludes characters inside the brackets.
b[!ae]ll finds bill and bull, but not ball or bell.
Like “[!a]*” finds all items that do not begin with the letter a.
Matches a range of characters. Remember to specify the characters in ascending order (A to Z, not Z to A).
b[a-c]d finds bad, bbd, and bcd.
Matches any single numeric character.
Examples of wildcard character pattern matching in expressions
To use a wildcard character within a pattern:
Open your query in Design view.
In the Criteria row of the field that you want to use, type the operator Like in front of your criteria.
Replace one or more characters in the criteria with a wildcard character. For example, Like R?308021 returns RA308021, RB308021, and so on.
On the Design tab, click Run.
Here are some examples of wildcard patterns that you can use in expressions:
C haracter(s)
Use to match
? or _ (underscore)
Any single character
Zero or more characters
Any single digit (0 — 9)
Any single character in charlist
Any single character not in charlist
Any alphanumeric character
Any of the uppercase letters in the range A through Z.
Note: When you specify a range of characters, the characters must appear in ascending sort. For example, [Z-A] is not a valid pattern.
Take a look at the basics of building an expression.
The CHARLIST function gives you matches for one or more characters and can include almost any characters in the ANSI character set, including digits. The CHARLIST is enclosed in brackets ([ ]) and can be used with wildcard characters for more specific matches.
To specify a range of characters, use CHARLIST with a hyphen (-) to separate the upper and lower bounds of the range.
To match the hyphen (-) character, put it at the beginning or end of CHARLIST (after the exclamation mark if you’re using one). In any other location, the hyphen identifies a range of ANSI characters.
A wildcard is a special character that lets you perform "fuzzy" matching on text in your Excel formulas. For example, this formula:
counts all cells in the range B5:B11 that end with the text "combo". And this formula:
Counts all cells in A1:A100 that contain exactly 3 characters.
Available wildcards
Excel has 3 wildcards you can use in your formulas:
- Asterisk (*) - zero or more characters
- Question mark (?) - any one character
- Tilde (~) - escape for literal character (~*) a literal question mark (~?), or a literal tilde (~~).
Example wildcard usage
Usage Behavior Will match ? Any one character "A", "B", "c", "z", etc. ?? Any two characters "AA", "AZ", "zz", etc. . Any three characters "Jet", "AAA", "ccc", etc. * Any characters "apple", "APPLE", "A100", etc. *th Ends in "th" "bath", "fourth", etc. c* Starts with "c" "Cat", "CAB", "cindy", "candy", etc. ?* At least one character "a", "b", "ab", "ABCD", etc. . -?? 5 characters with hypen "ABC-99","100-ZT", etc. *~? Ends in question mark "Hello?", "Anybody home?", etc. *xyz* Contains "xyz" "code is XYZ", "100-XYZ", "XyZ90", etc. Wildcards only work with text. For numeric data, you can use logical operators.
Compatible functions
Not all functions allow wildcards. Here is a list of the most common functions that do:
To perform a partial match with XLOOKUP, supply 2 for the match mode argument to allow wildcards. In the example shown, the formula in H7 is:
which performs a wildcard match with the value in H4 and returns all 4 fields as the result. The TRANSPOSE function is optional and used here only to convert the result from XLOOKUP to a vertical array.
Working from the inside out, XLOOKUP is configured to find the value in H4 in the Last name column, and return all fields. In order to support wildcards, match_mode is provided as 2:
- The lookup_value comes from cell H4
- The lookup_array is the range D5:D15, which contains Last names
- The return_array is B5:E15, which contains all all fields
- The not_found argument is set to "Not found"
- The match_mode is is 2, to allow wildcards
- The search_mode is not provided and defaults to 1 (first to last)
Since H4 contains "corr*", XLOOKUP finds the first Last name beginning with "corr" and returns all four fields in a horizontal array:
This result is returned directly to the TRANSPOSE function:
The TRANSPOSE function changes the array from horizontal to vertical:
and the array values spill into the range H7:H10.
With implicit wildcard
In the example above, the asterisk wildcard (*) is entered explicitly into the lookup value. To pass in the wildcard implicitly, you can adjust the formula like this:
Above, we concatenate the asterisk wildcard (*) to the value in H4 in the formula itself. This will append the asterisk to any value entered in H4, and XLOOKUP will perform a wildcard lookup.
Читайте также: