Убрать все запятые из ячеек Excel можно через «Найти и заменить»: Ctrl+H, в поле «Найти» — запятая, поле «Заменить на» пустое, «Заменить все». Формулой то же делает =ПОДСТАВИТЬ(A2;",";""). Удалить сразу все знаки препинания и оставить только буквы, цифры и пробелы можно регулярным выражением =СЖПРОБЕЛЫ(REGEXREPLACE(A2;"[^\sa-zA-Zа-яА-ЯёЁ0-9]";" ")) (в Excel 365 — встроенная РЕГЗАМЕНИТЬ) или процедурой !SEMTools в один клик.
Зачастую при анализе текстовых данных в Excel необходимо удалить из текста ячеек знаки препинания, чтобы оставить только буквы и цифры.
Обычная последовательная замена каждого из пунктуационных символов в диапазоне займет много времени
Одно из возможных решений - массовая замена значений в Excel. Но гарантии того, что этот метод позволит удалить всю пунктуацию в диапазоне - не будет. Глаза могут просто пропустить некоторые символы. Особенно, если объем текста велик.
Все это сложно и требует много времени. Есть ли способы удалить пунктуацию побыстрее? Да, есть!
Убрать запятые в ячейках Excel
- Выделите диапазон (или ничего не выделяйте, чтобы заменить на всём листе).
- Нажмите Ctrl+H.
- В поле «Найти» введите запятую, поле «Заменить на» оставьте пустым.
- Нажмите «Заменить все».
Чтобы не затронуть исходные данные, используйте формулу =ПОДСТАВИТЬ(A2;",";"") в соседнем столбце. Если нужно заменить запятую на точку (например, в дробных числах для выгрузки), укажите точку в поле «Заменить на» или в третьем аргументе ПОДСТАВИТЬ.
Убрать запятую в числах
Если запятая — часть числового формата (дробная часть «1250,00» или разделитель разрядов), заменой её не убрать: это не символ, а отображение. Откройте Ctrl+1 → «Число» и уменьшите «Число десятичных знаков» до 0 или снимите галочку «Разделитель групп разрядов». Чтобы убрать дробную часть в самом значении, используйте округление.
| A | B | C | |
|---|---|---|---|
| 1 | Текст | Без запятых | Без пунктуации |
| 2 | Привет, мир! | Привет мир!=ПОДСТАВИТЬ(A2;",";"") | Привет мир=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"[^\sa-zA-Zа-яА-ЯёЁ0-9]";" ")) |
| 3 | iPhone 15 (Pro) | iPhone 15 (Pro)=ПОДСТАВИТЬ(A3;",";"") | iPhone 15 Pro=СЖПРОБЕЛЫ(REGEXREPLACE(A3;"[^\sa-zA-Zа-яА-ЯёЁ0-9]";" ")) |
| 4 | цена: 1 200 руб. | цена: 1 200 руб.=ПОДСТАВИТЬ(A4;",";"") | цена 1 200 руб=СЖПРОБЕЛЫ(REGEXREPLACE(A4;"[^\sa-zA-Zа-яА-ЯёЁ0-9]";" ")) |
Регулярное выражение заменяет на пробел всё, кроме букв (латиница и кириллица, включая ё), цифр и пробелов, а СЖПРОБЕЛЫ убирает получившиеся лишние пробелы. Латинские заглавные буквы (P в Pro) сохраняются.
В английском Excel: =TRIM(REGEXREPLACE(A2,"[^\sa-zA-Zа-яА-ЯёЁ0-9]"," "))
Удалить знаки препинания формулой в Excel 365
Эта формула будет работать только в последних версиях Excel, где поддерживается функция СЦЕП, необходимая для объединения массива. Она удалит из текста все символы, кроме букв, способных менять регистр (быть заглавными и строчными), цифр и пробелов.
А вот и сама формула:
=СЖПРОБЕЛЫ(СЦЕП(ЕСЛИ(СОВПАД(СТРОЧН(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ПОДСТАВИТЬ(A1;" ";"ѫ"))));1));ПРОПИСН(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ПОДСТАВИТЬ(A1;" ";"ѫ"))));1)));ЕСЛИОШИБКА(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)+0;" ");ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(ПОДСТАВИТЬ(A1;" ";"ѫ"))));1))))Удалить пунктуацию с помощью регулярных выражений
Поскольку слова в русскоязычных текстах, как правило, состоят только из символов кириллицы, латиницы и цифр - наиболее простым способом быстро оставить их - применить синтаксис регулярных выражений.
Подробнее о них можно почитать в этой статье. Если кратко, в Excel для Microsoft 365 есть встроенная функция РЕГЗАМЕНИТЬ (REGEXREPLACE), а в Excel 2010–2021 регулярные выражения доступны через VBA или надстройку.
Эту задачу я решил за вас, все что остается - подключить надстройку !SEMTools к вашему Excel, и функции начнут работать. Причем эти функции доступны в бесплатной версии !SEMTools.
Регулярное выражение для удаления всех символов, кроме букв, цифр и пробелов, будет таким:
=СЖПРОБЕЛЫ(REGEXREPLACE(A1;"[^\sa-zA-Zа-яА-ЯёЁ0-9]";" "))
Как работает формула:
- Функция REGEXREPLACE заменяет все символы, кроме перечисленных диапазонами в квадратных скобках, на пробел;
- СЖПРОБЕЛЫ удаляет лишние пробелы.
Обратите внимание: в старой версии этой формулы в шаблоне не было диапазона A-Z, и заглавные латинские буквы удалялись вместе с пунктуацией. В формуле выше это исправлено. В Excel 365 то же самое — =СЖПРОБЕЛЫ(РЕГЗАМЕНИТЬ(A1;"[^\sa-zA-Zа-яА-ЯёЁ0-9]";" ")).
Удалить пунктуацию в один клик
У формул регулярных выражений в Excel, как у той, что выше, есть некоторые не очень существенные, но недостатки. Они:
- требуют время на написание,
- требуют создания отдельного столбца,
- удалят диакритические символы, если их не указать вручную (а их огромное количество, вот, например, все варианты диакритических символов, похожих на букву A - Ă Ắ Ặ Ằ Ẳ Ẵ Ǎ Â Ấ Ậ Ầ Ẩ Ẫ Ä Ǟ Ȧ Ǡ Ạ Ȁ À Ả Ȃ Ā Ą Å Ǻ Ḁ Ⱥ Ã).
Для тех, кто, как и я, не ищет компромиссов касательно экономии своего времени, в я разработал процедуру !SEMTools, которая избавляет от перечисленных проблем. Попробовать можно и в бесплатной версии, но обработано будет 2/3 данных.
Алгоритм использования прост и показан ниже на анимации:
- Выделяем столбец с исходными фразами,
- Находим макрос в меню "УДАЛИТЬ" -> Символы -> пунктуацию,
- Готово!
Почему пунктуация удаляется не так
- Пропали латинские заглавные буквы. В шаблоне регулярного выражения нет диапазона A-Z. Используйте [^\sa-zA-Zа-яА-ЯёЁ0-9].
- Слова «слиплись». Знак заменили на пустоту, а не на пробел: «Привет,мир» → «Приветмир». Заменяйте на пробел и оборачивайте результат в СЖПРОБЕЛЫ.
- Из чисел пропали дробные части. Запятая в «2,5» — тоже знак препинания для шаблона. Если числа нужно сохранить, добавьте запятую в разрешённые символы: [^\sa-zA-Zа-яА-ЯёЁ0-9,].
- Удалились буквы с диакритикой (é, ü, ñ). Шаблон их не перечисляет. Добавьте нужные буквы в квадратные скобки или используйте процедуру !SEMTools.
- Ctrl+H заменил запятые во всей книге. В параметрах было выбрано «Искать: в книге». Отмените действие и выберите «на листе».
Удаление пунктуации — частые вопросы
Как убрать все запятые в Excel?
Ctrl+H → «Найти»: запятая, «Заменить на»: пусто → «Заменить все». Или формулой =ПОДСТАВИТЬ(A1;",";"").
Как удалить все знаки препинания из строки?
Формулой =СЖПРОБЕЛЫ(REGEXREPLACE(A1;"[^\sa-zA-Zа-яА-ЯёЁ0-9]";" ")) или процедурой !SEMTools «Удалить → Символы → Пунктуацию».
Как называется REGEXREPLACE в русском Excel?
В Excel для Microsoft 365 — РЕГЗАМЕНИТЬ. В надстройке !SEMTools функция называется REGEXREPLACE в любой версии.
Как убрать запятую в числовом формате?
Через Ctrl+1 → «Число»: уменьшите число десятичных знаков или снимите «Разделитель групп разрядов».
Удалить все знаки препинания и спецсимволы — сохранив буквы любых алфавитов, включая диакритику, — сразу во всём диапазоне можно в один клик с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как удалить знаки препинания в Excel
| Задача | Способ |
|---|---|
| Убрать запятые | Ctrl+H или =ПОДСТАВИТЬ(A1;",";"") |
| Вся пунктуация, оставить буквы и цифры | =СЖПРОБЕЛЫ(REGEXREPLACE(A1;"[^\sa-zA-Zа-яА-ЯёЁ0-9]";" ")) |
| То же в Excel 365 | РЕГЗАМЕНИТЬ с тем же шаблоном |
| Запятая в числовом формате | Ctrl+1 → «Число» |
| С сохранением диакритики, в 1 клик | Процедура !SEMTools |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: удалить лишние пробелы и массовая замена значений.