Формула Excel: Нормализация текста
Иногда при импорте текстовых строк с других устройств или из внешних источников они могут содержать лишние знаки препинания, избыточные пробелы или быть полностью набраны заглавными буквами — всё это необходимо удалить и привести в порядок. В этом руководстве представлена формула, объединяющая функции НИЖН, СЖПРОБЕЛЫ и ПОДСТАВИТЬ для эффективного решения этой задачи.
Универсальная формула:
| =LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(text,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," "))) |
Аргументы
| Text: the text string you want to normalize. |
Возвращаемое значение:
Эта формула удаляет знаки препинания и лишние пробелы, а также преобразует текст из верхнего регистра в нижний для фильтрации по строкам.
Как работает эта формула
Например, нормализуйте текст в диапазоне B3:B5 и выведите результаты в диапазон C3:C5. Для этого введите приведённую ниже формулу в ячейку C3:
| =LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(B3,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," "))) |
Нажмите клавишу Enter, а затем перетащите маркер заполнения вниз до ячейки C5, чтобы получить нормализованный текст из диапазона B3:B5. 
Пояснение
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE()))))))
SUBSTITUTE(текст;«(»;« »);«)»;« »);"-";« »);":";« »);";";« »);«!»;« »);",";« »);".";« »): функция ПОДСТАВИТЬзаменяет указанные символы на новые в текстовой строке. В данном случае формула заменяет знаки препинания на пробелы.
СЖПРОБЕЛЫ: функция СЖПРОБЕЛЫ удаляет лишние пробелы из текстовой строки.
НИЖН: функция НИЖН преобразует заглавные буквы в строчные.
Примечание
Если необходимо сохранить начальные или Пробелы в конце в текстовой строке, используйте следующую формулу:
| =" "&LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(B3,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))&" " |
Пример файла
Нажмите, чтобы скачать пример файла
Связанные формулы
- Проверка, содержит ли ячейка определённый текст
Чтобы проверить, содержит ли ячейка любой текст из диапазона A, но при этом не содержит ни одного текста из диапазона B, используйте формулу массива, сочетающую функции СЧЁТ, ПОИСК и И в Excel. - Проверка, содержит ли ячейка одно из нескольких значений, исключая другие
В этом руководстве представлена формула для быстрой проверки: содержит ли ячейка хотя бы одно из заданных значений, но при этом не содержит других. Также подробно разобраны аргументы этой формулы в Excel. - Проверка, содержит ли ячейка одно из значений
Допустим, в Excel в столбце E указан список значений. Нужно проверить, содержат ли ячейки столбца B хотя бы одно из этих значений, и вернуть ИСТИНА или ЛОЖЬ. - Проверка, содержит ли ячейка число
Иногда бывает нужно проверить, содержит ли ячейка цифры. В этом руководстве вы найдёте формулу, которая возвращает ИСТИНА, если в ячейке есть число, и ЛОЖЬ — если его нет.
Лучшие инструменты для повышения продуктивности в Office
Kutools для Excel — Помогает Вам выделяться из толпы
Kutools для Excel предлагает более 300 функций,гарантируя, что всё необходимое всегда под рукой…
Office Tab — Включает вкладочное чтение и редактирование в Microsoft Office (включая Excel)
- Переключайтесь между десятками открытых документов всего за секунду!
- Сокращает сотни кликов мышью каждый день — забудьте о «мышечной руке» раз и навсегда!
- Повышает продуктивность на 50 % при одновременном просмотре и редактировании нескольких документов.
- Добавляет эффективность вкладок в Office (включая Excel) — как в Chrome, Edge и Firefox.