Подстановочные знаки (символы *, ? и ~) в Excel

Рассмотрим применение подстановочных знаков в Excel (символы звездочки «*», тильды «~» и вопросительного знака «?») и их использование при поиске и замене текстовых значений.


Приветствую всех, дорогие читатели блога TutorExcel.Ru.

В начале предлагаю вспомнить определение подстановочных знаков и понять, что же это такое и для каких целей они применяются в Excel. А затем уже разберем применение на конкретных примерах.
Подстановочные знаки — это специальные символы, которые могут принимать вид любого произвольного количества символов, другими словами, являются определенными масками комбинаций символов.
Всего в Excel есть 3 типа подобных знаков:

  • * (звездочка); Обозначает любое произвольное количество символов.
    Например, поиск по фразе «*ник» найдет слова типа «понедельник», «всадник», «источник» и т.д.
  • ? (вопросительный знак); Обозначает один произвольный символ.
    К примеру, поиск по фразе «ст?л» найдет «стол», «стул» и т.д.
  • ~ (тильда) с последующими знаками *, ? или ~. Обозначает конкретный символ *, ? или ~.
    Например, поиск по фразе «хор*» найдет все фразы начинающиеся на «хор» («хоровод», «хорошо» и т.д.). Поэтому для точного поиска «хор*» нужно использовать символ «~» и искать по фразе «хор~*». Наличие «~» гарантирует, что Excel прочитает следующий символ как текст, а не как подстановочный знак.

Использование таких спецсимволов может быть полезно при фильтрации данных, для сравнения текста, при поиске и замене текстовых значений. Давайте подробно остановимся на каждом из основных вариантов применения.

Фильтрация данных

Рассмотрим пример. Предположим, что у нас имеется список сотрудников компании и мы хотим отфильтровать только тех сотрудников, у которых фамилии начинаются на конкретную букву (к примеру, на букву «п»):

Список сотрудников
Для начала добавляем фильтр на таблицу (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L).
Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):

Применение фильтра
Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:

Фильтрация данных
В общем случае при фильтрации данных мы можем использовать абсолютно любые критерии, никак не ограничивая себя в выборе маски поиска (произвольный текст, различные словоформы, числа и т.д.).
К примеру, чтобы показать все варианты фамилий, которые начинаются на букву «к» и содержат букву «в», то применим фильтр «к*в*» (т.е. фраза начинается на «к», затем идет произвольный текст, потом «в», а затем еще раз произвольный текст).
Или поиск по «п?т*» найдет фамилии с первой буквой «п» и третьей буквой «т» (т.е. фраза начинается на «п», затем идет один произвольный символ, затем «т», и в конце опять произвольный текст).

Применение в функциях

Как уже говорилось выше, подстановочные знаки в Excel могут использоваться в качестве критерия при сравнении текста в различных функциях Excel (например, СЧЁТЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН, ГПР, ВПР и другие).

Повторим задачу из предыдущего примера и подсчитаем количество сотрудников компании, фамилии которых начинаются на букву «п».
Воспользуемся функцией СЧЁТЕСЛИ, которая позволяет посчитать количество ячеек соответствующих указанному критерию.
В качестве диапазона данных укажем диапазон с сотрудниками (A2:A20), а в качестве критерия укажем запись «п*» (т.е. любая фраза начинающаяся на букву «п»):

Сравнение текста
Как и в первом примере, в результате мы получили ровно 3 фамилии.

Однако не все функции поддерживают применение подстановочных знаков. Некоторые из них (к примеру, функция НАЙТИ) любой символ воспринимают как текст, даже несмотря на то, что он может быть служебным.
С помощью функции НАЙТИ найдем в тексте позицию вхождения вопросительного знака и звездочки:

Функция НАЙТИ
Обратным примером служит аналогичная функция ПОИСК, в которой мы должно четко указать что ищем именно служебный символ:

Функция ПОИСК
Как видим результат у функций получился одинаковым, однако обращение к подстановочным знакам разное.

Инструмент «Найти и заменить»

Подстановочные знаки в Excel также можно использовать для поиска и замены текстовых значений в инструменте «Найти и заменить» (комбинация клавиш Ctrl + F для поиска и Ctrl + H для замены).

Рассмотрим пример. Имеется список продукции магазина, в котором нам нужно найти продукт «молоко».
Предположим, что при вводе данных сделали ошибки из-за чего в списке появились продукты «малоко».

Чтобы несколько раз не искать данные по словам «молоко» или «малоко», при поиске воспользуемся критерием «м?локо» (т.е. вторая буква — произвольная):

Инструмент "Найти и заменить"
При этом не стоит забывать, что с помощью данного инструмента можно не только искать текст, но и заменять его (к примеру, заменить «м?локо» на «молоко»).

Как заменить звездочку «*» в Excel?

Практически наверняка каждый сталкивался со следующей ситуацией — в тексте присутствует символ звездочки, который необходимо удалить или заменить на какой-либо другой текст.
Однако при попытке заменить звездочку возникают трудности — при замене меняются абсолютно весь текст, что естественно и логично, так как Excel воспринимает символ «*» как любой произвольный текст.
Но мы теперь уже знаем как с этим бороться, поэтому в поле Найти указываем текст «~*» (явно показываем, что звездочка является специальным символом), а в поле Заменить на указываем на что заменяем звездочку, либо оставляем поле пустым, если хотим удалить звездочку:

Удаление звездочки
Аналогичная ситуация и при замене или удалении вопросительного знака и тильды.
Производя замену «~?» (для тильды — «~~») мы также без проблем сможем заменить или удалить спецсимвол.

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru

Поделиться с друзьями:
Поиск по сайту:

33 идей о “Подстановочные знаки (символы *, ? и ~) в Excel

  1. Валерий

    Пробую в Excel найти номер телефона: 1234567890. Сам номер неизвестен, но известно что он состоит из 10-ти знаков и находится один в ячейке без дополнительных слов и знаков. Даю маску поиска: ?????????? — т.е. покажи десять любых знаков в отдельной ячейке. НЕ НАХОДИТ! ПОЧЕМУ?

    1. tutorexcel Автор поста

      С такой маской Excel будет искать не ровно 10 знаков, а все ячейки где есть хотя бы 10 знаков (т.е. 10, 11, 12, … знаков).

      1. Алексей

        Какую формулу и маску использовать если в ячейке есть текст и номер, но мы знаем что номер состоит из 4 цыфр?

        1. tutorexcel Автор поста

          Формально подстановочные знаки в Excel не различают какой именно текст перед ними(буквы или цифры), поэтому маской такую комбинацию не задать.

  2. Вадим

    Подстановочные знаки (символы *, ? и ~) хорошо работают для поля «Найти:», а можно ли их использовать в поле «Заменить:»

    Например: диапазон из 3 ячеек имеет текст:
    2019
    2017
    2018

    Как с получить результат:
    2019 г.
    2017 г.
    2018 г.

    Спасибо.

  3. Дмитрий Марков

    Число из текста в ячейке A1:

    =ЛЕВСИМВ(A1;4)
    =ПОДСТАВИТЬ(A1;» г.»;»»)
    {=МАКС(ЕСЛИОШИБКА(—ПСТР(A1;СТРОКА($1:$6);СТОЛБЕЦ(A:L));))}

    …, что Вам больше нравится

  4. Анастасия

    =ЕСЛИ(D3/B3>СРЗНАЧ($D$3/$B$3;$D$4/$B$4;$D$5/$B$5;$D$6/$B$6;$D$7/$B$7;$D$8/$B$8;$D$9/$B$9);A3;» «)

    хочу сжать то, что написано в скобках СРЗНАЧ, чтобы Эксель автоматически выполнял это действие,.

    Думаю, что нужно между первой и последней дробью для этого, нужно поставить знак

    =ЕСЛИ(D3/B3>СРЗНАЧ($D$3/$B$3;;$D$9/$B$9);A3;» «) но не знаю какой

  5. Михаил

    Добрый день , задача простая, но не смог найти решения. Есть большое кол-во значений в столбце , необходимо найти значение только чисел с значением до сотых. Т.е не округлить, а именно найти (пример 2,02 подходит 2,022 не подходит) . В идеале вывод в сводную таблицу или поиск значения наиболее близкого к заданному.

    1. tutorexcel Автор поста

      Подстановочные знаки не различают что перед ними (буква или цифра), поэтому найти именно число с их помощью не получится.
      Но к Вашей задаче можно попробовать подойти с другой стороны.
      Если нужно найти все числа с сотыми долями, то у всех таких чисел 3 знак с конца обязательно запятая (или точка, в зависимости от того, что используется для разделения).
      Поэтому с помощью формулы =ЕСЛИ(ЕЧИСЛО(A1)=ИСТИНА;ЕСЛИ(ПСТР(A1;ДЛСТР(A1)-2;1)=»,»;1;0);0) мы сначала проверяем число ли в ячейке (A1), а потом проверяем какой знак стоит третьим с конца, если запятая, то число подходит, в остальных случаях нет.

  6. Антон

    Добрый день! Подскажите, пожалуйста, как в в столбце с номерами телефонов найти и удалить те номера, которые короче, чем 11 знаков?
    Спасибо!

    1. tutorexcel Автор поста

      Антон, добрый день!
      Можно с помощью функции ДЛСТР (определяет длину строки) сделать проверку на длину номера, если значение меньше 11, то удаляем, если больше то оставляем.
      Если именно через подстановочные знаки решать эту задачу, то попробуйте в поиске ввести 11 знаков «?», Excel найдет все номера где 11 и более символов, все остальное тогда можно удалять.

  7. Евгений

    Есть список товаров, в названии которого встречается «*», в следствии чего функция работает некорректно. Можно ли как-то экранировать такие символы?

    1. tutorexcel Автор поста

      А какая конкретно функция не работает?
      Можете поподробнее Вашу задачу описать.

  8. Юрий

    Добрый день! Имеется таблица, два столбца, данные используются в формулах.
    79 A
    80 R
    81 A
    82 R
    83 R
    W01NA R
    01 A
    02 A
    03 R

    Задача привести при печати вид ячеек к типу, где во втором столбце «R» — W82R, где во втором столбце «А» — W79. С цифрами проблем не возникает, прописал в формате ячейки «W»0#»R». А вот с ячейкой типа W01NA никак не могу найти способ как не меняя значения в ячейке получить на печати W01NAR.

    1. tutorexcel Автор поста

      Так как W01NA не число, а текст, то тут сможем этим как раз и воспользоваться.
      Для чисел пишем «W»0#»R», для текста @»R» (т.е. просто в случае текста добавляем к нему справа букву), в итоге формат будет следующий: «W»0#»R»;@»R»

  9. Алексей

    Пришел по почте в теле письма заказ:

    234334 — 3 шт
    678543 — 1 шт

    Как разделить по столбикам 1 и 2е число?
    Например
    1й столбик 234334, 2й столбик 3
    И ТД по строкам

  10. Егор

    Добрый день!
    Подскажите пожалуйста как удалить строки содержащие знак вопроса «?» ?
    Через ~? поиском и заменой не находит.
    ?????? ???????? (????????)
    ?? 340 ?
    ??????????

    1. tutorexcel Автор поста

      По описанию все делаете правильно. Поиск по «~?» найдет все ячейки где есть знак вопроса, после чего их можно удалить.
      К сожалению, сложно сказать в чем может быть проблема.

  11. Павел

    Здравствуйте! Помогите, пожалуйста, в сводной таблице я хочу сделать маску в стилях ячеек для чтобы кто не набрал процент по выработке, чтобы выделялись ячейки другим цветом. А как он набирает процент выработки автоматически менялся на чёрный. Заранее спасибо!

    1. tutorexcel Автор поста

      Да, по идее можно, попробуйте добавить в эти ячейки условное форматирование с заданным правилом.

  12. Анатолий

    Добрый день! Столкнулся с такой проблемой: В наличии огромный масив данных( более миллиона строк) необходимо отфильтавать, оставить строки в которых содержаться знаки препинания, цифры, буквы верхнего и нижнего регистра. Возможно создать такой фильтр?

  13. Рустам

    Как можно изменит линий подчёркивания на *********** (звёздочку) при вводе текста

    1. tutorexcel Автор поста

      Заменить на звездочку можно точно так же как и на любой другой символ — через инструмент «Найти и заменить», через функцию ПОДСТАВИТЬ и пр.
      Со звездочкой могут возникнуть проблемы, если мы меняем саму звездочку (т.е. в случае когда звездочка не заменяющий символ, а заменяемый), тогда нужно аккуратно прописывать подстановочные знаки и использовать «~».

  14. Верст

    Добрый день.
    В таблице создал в диспетчере имен диапазон со списком слов, мне надо выделять ячейки со списками словосочетаний которые находятся на другом листе, но только те ячейки которые содержат слова из списка из диспетчера имен.
    Делаю такое выделение по формуле =ИЛИ(ЕЧИСЛО(ПОИСК(стоп;RC))) через правила форматирования, все работает., Но если в диапазоне имен заданно слово «мор», а в искомом списке есть слово «пришел дед мороз», то эта ячейка выделяется так как в ней в слове «мороз» есть слово «мор», а мне надо чтобы выделялись только те ячейки где есть точное вхождение слова «мор».
    Подскажите пожалуйста как сделать чтобы выделялись только слова с точным значением.

  15. Леонид

    Добрый день, прошу помощи. Как найти все числа в столбце у которых 2 знака после запятой остальные нули, остальные числа с большим количеством знаков после запятой отмести? при этом количество знаков целого числа до запятой может быть любым (грубо говоря числа типа *,ХХ00000 найти, числа типа *,ХХХ000, *,ХХХХ000 и т.д. отмести)

    1. tutorexcel Автор поста

      Как вариант можно умножить число на 100, если оно будет целым, то перед Вами *,ХХ000, если не целым, то *,ХХХ??

  16. Светлана

    Добрый день!

    В ячейке прописаны номера телефонов в таком формате:

    +7 (499) 785-29-57
    +7 (495) 642-44-27
    +7 916 313-00-79
    +7 965 194-81-91
    +7 906 018-81-91
    +7 906 019-81-91

    Как можно удалить номера с кодом города (499, 495), а оставшиеся номера привести к формату:

    79163130079
    79651948191
    и т.д.

    И таких ячеек в таблице около 10 тысяч.

    Сама пока не придумала, как это сделать)

  17. Елена

    Добрый день!
    Необходимо удалить звездочки. Я прочитала, что надо использовать для замены комбинацию ~*
    Но после звездочки стоит номер из 20 цифр (например *42306810710000001570), который после замены звездочки становиться не читаемым (4.23068E+19).
    .

    1. YuraS

      Если необходимо число как текст, то поменять формат ячеек на текстовый или менять звездочку на одинарную кавычку

    2. YuraS

      Заменить звездочку на одинарную кавычку, в ячейках она видна не будет, только в поле для редактирования.

  18. Ирина

    Добрый день! Прошу помощи с таблицей! Есть первая таблица с номерами заказов и есть таблица с платежными поручениями, где в назначении платежа указаны номера заказов. Подскажите пжлст, как подтянуть в первую таблицу суммы платежных поручений, в соответствии с номерами заказов из платежек? Номера заказов восьмизначные и начинаются всегда на 100, пыталась с помощью подстановочных знаков (но видимо что-то делаю не так.и с помощью какой функции лучше делать это? Заранее спасИбо!

Добавить комментарий для Светлана Отменить ответ

Ваш адрес email не будет опубликован.