Удалить знаки препинания в Excel

Зачастую при анализе текстовых данных в Excel необходимо удалить из текста ячеек знаки препинания, чтобы оставить только буквы и цифры.

Обычная последовательная замена каждого из пунктуационных символов в диапазоне займет много времени

Одно из возможных решений – массовая замена значений в Excel. Но гарантии того, что этот метод позволит удалить всю пунктуацию в диапазоне – не будет. Глаза могут просто пропустить некоторые символы. Особенно, если объем текста велик.

Все это сложно и требует много времени. Есть ли способы удалить пунктуацию побыстрее? Да, есть!

Формула для Excel 365

Эта формула будет работать только в последних версиях Excel, где поддерживается функция СЦЕП, необходимая для объединения массива. Она удалит из текста все символы, кроме букв, способных менять регистр (быть заглавными и строчными), цифр и пробелов.

А вот и формула:

=СЖПРОБЕЛЫ(СЦЕП(ЕСЛИ(СОВПАД(СТРОЧН(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ПОДСТАВИТЬ(A1;" ";"ѫ"))));1));ПРОПИСН(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ПОДСТАВИТЬ(A1;" ";"ѫ"))));1)));ЕСЛИОШИБКА(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)+0;" ");ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ПОДСТАВИТЬ(A1;" ";"ѫ"))));1))))

Удалить пунктуацию с помощью регулярных выражений

Поскольку слова в русскоязычных текстах, как правило, состоят только из символов кириллицы, латиницы и цифр – наиболее простым способом быстро оставить их – применить синтаксис регулярных выражений.

Подробнее о них можно почитать в этой статье. Если кратко, их поддержки в Excel “из коробки” нет, но подключить библиотеку с помощью VBA можно.

Эту задачу я решил за вас, все что остается – подключить надстройку !SEMTools к вашему Excel, и функции начнут работать. Причем эти функции доступны в бесплатной версии !SEMTools.

Регулярное выражение для удаления всех символов, кроме букв, цифр и пробелов, будет таким:

=СЖПРОБЕЛЫ(REGEXREPLACE(A1;"[^\sa-zа-яА-ЯёЁ0-9]";" "))

Как работает формула:

1. Функция REGEXREPLACE заменяет все символы, кроме перечисленных диапазонами в квадратных скобках, на пробел;
2. СЖПРОБЕЛЫ удаляет лишние пробелы.

Удалить пунктуацию в один клик

У формул регулярных выражений в Excel, как у той, что выше, есть некоторые не очень существенные, но недостатки. Они:

• требуют время на написание,
• требуют создания отдельного столбца,
• удалят диакритические символы, если их не указать вручную (а их огромное количество, вот, например, все варианты диакритических символов, похожих на букву A – Ă Ắ Ặ Ằ Ẳ Ẵ Ǎ Â Ấ Ậ Ầ Ẩ Ẫ Ä Ǟ Ȧ Ǡ Ạ Ȁ À Ả Ȃ Ā Ą Å Ǻ Ḁ Ⱥ Ã).

Для тех, кто, как и я, не ищет компромиссов касательно экономии своего времени, в я разработал процедуру !SEMTools, которая избавляет от перечисленных проблем. Попробовать можно и в бесплатной версии, но обработано будет 2/3 данных.

Алгоритм использования прост и показан ниже на анимации:

1. Выделяем столбец с исходными фразами,
2. Находим макрос в меню “УДАЛИТЬ” -> Символы -> пунктуацию,
3. Готово!