Наверное кто-то уже сталкивался с подобной ситуацией - на листе в ячейках записаны слова/предложения, в которых имеется знак - * - звездочка. Иногда его нужно либо найти, либо заменить на другой символ, либо просто удалить. Например, строку вида "496*120*45" надо преобразовать в такой: "496x120x45". И вот тут как раз возникает проблема: символ звездочки(как и знак вопроса - ?) является служебным символом - он заменяет собой группу любых символов(а знак вопроса - один любой символ). И при попытке заменить только звездочку Excel заменят ВСЕ данные в ячейке. Пока я имею ввиду только стандартную замену на листе:

    - Excel 2003: Правка(Edit) -Заменить(Replace)
    - Excel 2007-2021: вкладка Главная(Home) -Найти и выделить(Find & Select) -Заменить(Replace)
    - или сочетание клавиш Ctrl+H

Неужели это такой баг и ничего нельзя сделать? Можно конечно. Разработчики предусмотрели подобную ситуацию. Все, что необходимо - это перед заменяемым символом звездочки(или другим служебным символом) необходимо поставить другой служебный символ - Тильда. Вот такой - ~. На обычной клавиатуре этот знак обычно расположен на кнопке вместе с буквой Ё. Символ Тильда сообщает Excel, что следующий за ним символ не выполняет свои служебные функции, а выступает в роли обычного символа. Т.е. если указать в поле поиска "~*", это будет означать поиск только звездочки как текста. Что нам и требовалось.

Таким образом после вызова окна замены текста мы в поле Найти(Find what) указываем текст - "~*"(в поле Заменить на(Replace With) указывается символ/ы, которым требуется заменить звездочку. Если звездочку надо удалить - поле оставляем пустым). И не стоит забывать про дополнительные параметры замены(они раскрываются при нажатии кнопки Параметры(Options) окна поиска). А именно - необходимо поставить/снять галочку у параметра Ячейка целиком(Whole cell или Match entire cell contents). В рассматриваемом случае эту галочку необходимо снять.

Если же надо найти саму Тильду, то необходимо перед ней указать...Тильду :) Т.е. указываем две тильды подряд: ~~


По тому же принципу этот символ используется и в формулах, которые работают с служебными символами. Например, есть такая функция - ПОИСК(SEARCH), которая ищет указанный символ или текст в заданной строке и возвращает номер позиции первого найденного символа. В этой функции обязательно применять тот же принцип, что описан выше - т.е. явно указывать Excel, что мы ищем спец.символ:
=ПОИСК("~~"; A1)
=ПОИСК("~*"; A1)
=ПОИСК("~?"; A1)
=SEARCH("~~", A1)
=SEARCH("~*", A1)
=SEARCH("~?", A1)

Символы звездочки и вопр.знака так же используются формулами ВПР(VLOOKUP), ГПР(HLOOKUP), ПОИСКПОЗ(MATCH), СУММЕСЛИ(SUMIF), СЧЁТЕСЛИ(COUNTIF) и т.д. Есть и другие функции, но я на примере ВПР хочу выделить один неприятный момент, с которым можно столкнуться в реальных задачах. Заключается он в том, что звездочка, вопросительный знак или сама тильда могут быть внутри текста в искомом массиве данных:
Таблица со служебными символами и ВПР
и как видно в этом случае данные с тильдой не могут быть найдены, т.к. ВПР воспринимает её как служебный символ и просто не ищет в исходных данных. Чтобы найти такие данные через ВПР(и прочие схожие функции) необходимо заменить тильду на две. Я бы советовал делать это при помощи функции ПОДСТАВИТЬ(SUBSTITUTE)(на примере формул из таблицы выше):
=ВПР(ПОДСТАВИТЬ(F2;"~";"~~");$A$2:$C$7;3;0)
=VLOOKUP(SUBSTITUTE(F2,"~","~~"),$A$2:$C$7,3,0)

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

И хоть данные со звездочкой и вопр.знаком в большинстве случаев мешать не будут, можно попасть на ситуацию, когда тексту со звездочкой будет сопоставлен неверный результат:
Таблица значений ВПР и звездочка
почему ВПР так поступает лучше прочитать здесь: Как найти значение в другой таблице или сила ВПР. А в этой статье я лишь приведу формулу, как избежать таких проблем. Так же как и с тильдой используем функцию ПОДСТАВИТЬ(SUBSTITUTE), но теперь подставляем тильду к звездочке:
=ВПР(ПОДСТАВИТЬ(D2;"*";"~*");$A$2:$A$4;1;0)
=VLOOKUP(SUBSTITUTE(D2,"*","~*"),$A$2:$A$4,1,0)

Но так же необходимо помнить, что не все функции работают со служебными символами. Некоторые формулы любые символы воспринимают как текст сразу и для них нет такого понятия, как служебный символ. К одной из таких функций относится функция НАЙТИ(FIND). Она делает тоже самое, что функция ПОИСК(SEARCH), только в ней для поиска служебного символа не надо ставить перед ним еще один, потому как она не воспринимает символы подстановки как таковые. Она абсолютно любой символ принимает как текст и ищет строго то, что указано:
=НАЙТИ("~"; A1)
=НАЙТИ("*"; A1)
=НАЙТИ("?"; A1)
=FIND("~", A1)
=FIND("*", A1)
=FIND("?", A1)

Так же игнорирует служебные символы и функция СУММПРОИЗВ(SUMPRODUCT). Полный перечень таких функций описывать смысла нет - их много и надо просто проверять эти моменты в зависимости от задач.

17 комментариев

  1. здравствуйте! подскажите плиз: есть значения -после перенесения из ворда в эксель-обрезало 0-т е нули в номерах тел получились типа 501112233 681112233, 661112233 и текст, цифры, др- между ними. нужно-3задачи: 1-я-добавить 0-т е нуль-это проще в опции найти и заменить или как?-но записываю в поле найти и заменить шаблон по количеству оставшихся в наличии цифр #########-пишет что "данные, удовлетворяющие поиску не обнаружены"; 2-я: проверить телефоны на повторяемость; 3-я задача-выбрать эти телефоны списком для экспорта в другой файл эксель или тхт формата-списком, другую с запятыми или без-написать только макрос можно? буду признателен Вам за совет

  2. Судя по заголовку тут должно объясняться и как удалить символ, а не только найти и заменить. Так как можно просто удалить звездочку?

  3. Огромное спасибо! Ровно то, что надо было. SPSS значимые данные помечает звездочками, а в таблице 84*84 клетки выискивать все вручную, мягко говоря, утомительно, куда удобнее выделение цветом по условию.

  4. А можно каким-либо образом через Найти и Заменить найти в выделенной группе ячеек текст (то что ищется и меняется запишу тут в кавычках), например: "(шт)" и заменить на служебный символ переноса строки и "(шт)"?
    Нужно что бы в ячейках где есть текст "(шт)" перед ним появился служебный символ переноса строки (как Alt+Enter).

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.