Общая формула
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))
Резюме
Чтобы избавиться от естественной сложности текста (разделение знаков препинания, нормализация регистра, удаление лишних пробелов), вы можете использовать формулу, основанную на функции ПОДСТАВИТЬ, с помощью функций ОБРЕЗАТЬ и СТЕПЕНЬ.
Контекст
Бывают случаи, когда вам нужно удалить часть изменчивости текста перед другой обработкой. Один из примеров - это когда вы хотите подсчитать определенные слова внутри больших текстовых строк. Поскольку Excel не поддерживает регулярные выражения, вы не можете создавать точные совпадения. Например, если вы хотите подсчитать, сколько раз слово «лиса» встречается в ячейке, вы в конечном итоге будете считать «лисы». Вы можете искать «лиса» (с пробелом), но это не поможет с «лисой» или «лисой». Один из способов решения проблемы - сначала упростить текст с помощью формулы во вспомогательном столбце, а затем выполнить подсчет в упрощенной версии. Пример на этой странице показывает один из способов сделать это.
Объяснение
В формуле, показанной в этом примере, используется ряд вложенных функций ПОДСТАВИТЬ для удаления скобок, дефисов, двоеточий, точек с запятой, восклицательных знаков, запятых и точек. Процесс выполняется изнутри, при этом каждая ЗАМЕНА заменяет один символ одним пробелом, а затем передается следующей ЗАМЕНЕ. Самая внутренняя ПОДСТАВКА удаляет левые круглые скобки, а результат передается следующей ЗАМЕЩЕНИЕ, которая удаляет правые скобки и так далее.
В версии ниже были добавлены разрывы строк для удобства чтения и упрощения редактирования замен. Excel не заботится о переносах строк в формулах, поэтому вы можете использовать формулу как есть.
= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))
После завершения всех замен результат обрабатывается функцией TRIM для нормализации пробелов, а затем функцией LOWER для перевода всего текста в нижний регистр.
Примечание. Вам необходимо настроить фактические замены в соответствии с вашими данными.
Добавление начального и конечного пробелов
В некоторых случаях вы можете добавить пробел в начало и конец очищенного текста. Например, если вы хотите точно подсчитывать слова, вы можете искать слово, окруженное пробелами (например, искать «лиса», «карта»), чтобы избежать ложных совпадений. Чтобы добавить начальный и конечный пробелы, просто объедините пробел ("") в начало и конец:
=" "&formula&" "
Где «формула» - более длинная формула выше.
