Формула Excel: Нормалізація тексту -

Зміст

Загальна формула

=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))

Резюме

Щоб усунути частину природної складності тексту (розділові знаки пунктуації, нормалізувати регістр, видалити зайві пробіли), ви можете використовувати формулу на основі функції ЗАМІНИ, за допомогою функцій TRIM та LOWER.

Контекст

Бувають випадки, коли вам потрібно видалити частину змінності тексту перед іншою обробкою. Один із прикладів - коли ви хочете порахувати конкретні слова всередині більших текстових рядків. Оскільки Excel не забезпечує підтримку регулярних виразів, ви не можете побудувати точні збіги. Наприклад, якщо ви хочете підрахувати, скільки разів у клітині з’являється слово «лисиця», ви в підсумку порахуєте «лисиць». Ви можете шукати "лисиця" (з пробілом), але це не вдасться з "лисицею" або "лисицею". Одним з обхідних шляхів є спрощення тексту спочатку за допомогою формули в допоміжному стовпці, а потім розраховується на спрощену версію. Приклад на цій сторінці показує один із способів зробити це.

Пояснення

Формула, показана в цьому прикладі, використовує ряд вкладених функцій ЗАМІНИ для вилучення дужок, дефісів, двокрапок, крапок з комою, знаків оклику, коми та крапок. Процес виконується зсередини, при цьому кожен ЗАМІНИТЬ замінює один символ на один пробіл, а потім передає наступному ЗАМІННИКУ. Самий внутрішній ЗАМІННИК видаляє ліві дужки, а результат передається наступному ЗАМІННИКУ, який видаляє праві дужки тощо.

У наведеній нижче версії додано розриви рядків для читабельності та полегшення редагування замін. Excel не дбає про розриви рядків у формулах, тому ви можете використовувати формулу як є.

= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))

Після того, як всі заміни завершені, результат запускається через TRIM для нормалізації пробілів, а потім функцію LOWER, щоб примусити весь текст в нижній регістр.

Примітка. Вам потрібно буде скоригувати фактичні заміни відповідно до ваших даних.

Додавання провідного та кінцевого простору

У деяких випадках вам може знадобитися додати пробіл до початку та кінця очищеного тексту. Наприклад, якщо ви хочете точно підрахувати слова, ви можете шукати слово, оточене пробілами (тобто шукати "лисиця", "карта"), щоб уникнути помилкових збігів. Щоб додати провідний та кінцевий пробіл, просто об’єднайте пробіл ("") на початок і кінець: