Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Как заменить/удалить/найти звездочку?


Наверное кто-то уже сталкивался с подобной ситуацией - на листе в ячейках записаны слова/предложения, в которых имеется знак - * - звездочка. Иногда его нужно либо найти, либо заменить на другой символ, либо просто удалить. Например, строку вида "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). Полный перечень таких функций описывать смысла нет - их много и надо просто проверять эти моменты в зависимости от задач.


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 17 комментариев
  1. Спасибо! Значек ~ простое решение проблемы, с которой я боролся несколько дней! Пишите еще статьи. Побольше.

  2. Евгений:

    Супер! Спасибо автору.

  3. Евгений:

    Да, но как быть если надо сделать, например, следующие замены?

    ****** -> (6)
    ***** -> (5)
    **** -> (4)
    *** -> (3)
    ** -> (2)
    * -> (1)

  4. Не совсем понял. Это имеется ввиду?
    Найти: ~*~*~*~*~*~*
    Заменить на: (6)

    Найти: ~*~*~*~*~*
    Заменить на: (5)

  5. Александр:

    Господа, добрый день, подскажите пожалуйста как в выделенном диапазоне произвести автоматически замену чисел 3 на 7 ?
    7245
    7246
    7247
    3248
    3249
    3250
    3251

  6. Выделить диапазон ячеек.
    Ctrl+H
    Найти: 3
    Заменить на: 7
    Снять галочку у параметра "Ячейка целиком".

  7. Елена:

    А как удалить звездочку?
    Если, как в вашем примере, то * меняются на -.
    А если поле "Заменить на" оставить пустым, то появляется сообщение "Поиск завершен. Выполнено 84 замен", но никаких изменений не происходит, все звездочки на месте.

  8. Елена, у меня все меняет как положено. Если Excel пишет, что замены были - значит они были. Проверьте форматы ячеек.

  9. Михаил:

    а возможно с помощью замены изменить так:
    456789 в 456-789
    учитывая что таких чисел в которые нужно вставить тире много разных?

  10. Александр:

    Спасибо!

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти