Наверное кто-то уже сталкивался с подобной ситуацией - на листе в ячейках записаны слова/предложения, в которых имеется знак - * - звездочка. Иногда его нужно либо найти, либо заменить на другой символ, либо просто удалить. Например, строку вида "496*120*45" надо преобразовать в такой: "496x120x45". И вот тут как раз возникает проблема: символ звездочки(как и знак вопроса - ?) является служебным символом - он заменяет собой группу любых символов(а знак вопроса - один любой символ). И при попытке заменить только звездочку Excel заменят ВСЕ данные в ячейке. Пока я имею ввиду только стандартную замену на листе:
Неужели это такой баг и ничего нельзя сделать? Можно конечно. Разработчики предусмотрели подобную ситуацию. Все, что необходимо - это перед заменяемым символом звездочки(или другим служебным символом) необходимо поставить другой служебный символ - Тильда. Вот такой - ~. На обычной клавиатуре этот знак обычно расположен на кнопке вместе с буквой Ё. Символ Тильда сообщает 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). Полный перечень таких функций описывать смысла нет - их много и надо просто проверять эти моменты в зависимости от задач.
Статья помогла? Поделись ссылкой с друзьями! Видеоуроки
Господа, добрый день, подскажите пожалуйста как в выделенном диапазоне произвести автоматически замену чисел 3 на 7 ?
7245
7246
7247
3248
3249
3250
3251
А как удалить звездочку?
Если, как в вашем примере, то * меняются на -.
А если поле "Заменить на" оставить пустым, то появляется сообщение "Поиск завершен. Выполнено 84 замен", но никаких изменений не происходит, все звездочки на месте.
Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Спасибо! Значек ~ простое решение проблемы, с которой я боролся несколько дней! Пишите еще статьи. Побольше.
Супер! Спасибо автору.
Да, но как быть если надо сделать, например, следующие замены?
****** -> (6)
***** -> (5)
**** -> (4)
*** -> (3)
** -> (2)
* -> (1)
Не совсем понял. Это имеется ввиду?
Найти: ~*~*~*~*~*~*
Заменить на: (6)
Найти: ~*~*~*~*~*
Заменить на: (5)
Господа, добрый день, подскажите пожалуйста как в выделенном диапазоне произвести автоматически замену чисел 3 на 7 ?
7245
7246
7247
3248
3249
3250
3251
Выделить диапазон ячеек.
Ctrl+H
Найти: 3
Заменить на: 7
Снять галочку у параметра "Ячейка целиком".
А как удалить звездочку?
Если, как в вашем примере, то * меняются на -.
А если поле "Заменить на" оставить пустым, то появляется сообщение "Поиск завершен. Выполнено 84 замен", но никаких изменений не происходит, все звездочки на месте.
Елена, у меня все меняет как положено. Если Excel пишет, что замены были - значит они были. Проверьте форматы ячеек.
а возможно с помощью замены изменить так:
456789 в 456-789
учитывая что таких чисел в которые нужно вставить тире много разных?
Спасибо!