Удалить лишние символы в ячейках Excel можно формулами: конкретный символ или фрагмент убирает =ПОДСТАВИТЬ(A2;"-";""), лишние пробелы — =СЖПРОБЕЛЫ(A2), первые N символов — =ПСТР(A2;N+1;1000), последние N — =ЛЕВСИМВ(A2;ДЛСТР(A2)-N), а целые группы символов (цифры, латиницу, пунктуацию) — регулярное выражение =REGEXREPLACE(A2;"\d";""). Ниже — пример формулы для каждой задачи и ссылки на подробные статьи.
Когда меня спрашивают, как удалить в Excel лишние символы, я не могу не задать ряд встречных вопросов:
- Что послужило причиной называть их лишними и избавиться от них?
- Что конкретно подразумевает процедура удаления? Мы будем непременно удалять их или заменим символы на какие-то другие, или, может быть, перенесем в другой столбец?
- Точно ли имеет смысл удалять сами символы? Может быть, стоит удалить из текста слова, в которых они содержатся? Или и вовсе содержимое ячеек целиком?
- Не проще ли вместо удаления этих символов рассматривать такую операцию, как извлечение из текста определенных символов кроме этих, удаляемых?
В зависимости от ответов на эти вопросы решений может быть много и разных. Где-то можно обойтись простейшими функциями, где-то подключить регулярные выражения, а где-то и вовсе понадобятся готовые программные решения. Итак, по порядку.
Удалить символы * и ?
См. Подстановочные символы в Excel.
| A | B | |
|---|---|---|
| 1 | Текст | Без * и ? |
| 2 | арт*125? | арт125=ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;"*";"");"?";"") |
| 3 | Что? Где? | Что Где=ПОДСТАВИТЬ(ПОДСТАВИТЬ(A3;"*";"");"?";"") |
В формуле ПОДСТАВИТЬ * и ? — обычные символы. В окне «Найти и заменить» перед ними нужна тильда: ~* и ~?, иначе Excel заменит всё содержимое.
В английском Excel: =SUBSTITUTE(SUBSTITUTE(A2,"*",""),"?","")
Удалить символы по их типу
MS Excel не предлагает удаление символьных множеств по их признаку, единственной процедурой для удаления всегда остается "найти и заменить", позволяющая удалять один символ или подстроку за раз. Но, если приложить некоторые усилия, все возможно.
Удалить все, кроме букв и цифр (удалить пунктуацию)
Удалить все символы, кроме букв и цифр, а иначе говоря, пунктуацию, — нетривиальная задача, ведь таких символов могут быть сотни! Но и она решается — смотрите статью на эту тему.
| A | B | |
|---|---|---|
| 1 | Текст | Только буквы и цифры |
| 2 | Привет, мир! (тест №1) | Привет мир тест 1=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"[^A-Za-zА-Яа-яЁё0-9 ]";"")) |
| 3 | e-mail: info@site.ru | email infositeru=СЖПРОБЕЛЫ(REGEXREPLACE(A3;"[^A-Za-zА-Яа-яЁё0-9 ]";"")) |
Шаблон оставляет латиницу, кириллицу, цифры и пробел, всё остальное удаляется. Подробнее — удалить знаки препинания.
Лишние пробелы
Наиболее часто ненужными считаются повторяющиеся пробелы между словами или пробелы в конце и начале ячейки. Как убрать их, можно узнать из этой статьи.
| A | B | |
|---|---|---|
| 1 | Текст | СЖПРОБЕЛЫ |
| 2 | купить диван | купить диван=СЖПРОБЕЛЫ(A2) |
| 3 | Москва - Тверь | Москва - Тверь=СЖПРОБЕЛЫ(A3) |
Пробелы по краям удаляются, между словами остаётся по одному. Неразрывные пробелы СЖПРОБЕЛЫ не видит — подробнее в статье лишние пробелы.
В английском Excel: =TRIM(A2)
Лишние символы справа / слева
Кто-то видит лишними символы справа или слева от основного текста в ячейке, желая отрезать их от него по позиции или по определенной границе. О том, как удалить N символов с начала или с конца каждой ячейки, читайте в этой статье.
| A | B | C | |
|---|---|---|---|
| 1 | Код | Без 3 первых | Без 3 последних |
| 2 | RU-12345-01 | 12345-01=ПСТР(A2;4;1000) | RU-12345=ЛЕВСИМВ(A2;ДЛСТР(A2)-3) |
ПСТР берёт текст начиная с 4-го символа, ЛЕВСИМВ — всё, кроме трёх последних. Подробнее — первые символы и последние символы.
В английском Excel: =MID(A2,4,1000)
В случае если границей является определенный символ и нужно удалить всё, что перед ним, поможет вот этот текст.
| A | B | |
|---|---|---|
| 1 | Текст | После двоеточия |
| 2 | Артикул: 125-А | 125-А=СЖПРОБЕЛЫ(ЗАМЕНИТЬ(A2;1;ПОИСК(":";A2);"")) |
| 3 | Город: Казань | Казань=СЖПРОБЕЛЫ(ЗАМЕНИТЬ(A3;1;ПОИСК(":";A3);"")) |
ПОИСК находит двоеточие, ЗАМЕНИТЬ стирает всё до него включительно, СЖПРОБЕЛЫ убирает пробел в начале. Подробнее — удалить текст до символа.
В английском Excel: =TRIM(REPLACE(A2,1,SEARCH(":",A2),""))
Цифры
Бывает, что ненужными символами становятся цифры, которых десять, и хочется более быстрый способ, чем очищать строки от них методом замены на пустоту. Как удалить цифры из текста в ячейках — этот раздел даст ответ на вопрос.
| A | B | |
|---|---|---|
| 1 | Текст | Без цифр |
| 2 | Диван 3 местный 2026 | Диван местный=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"\d";"")) |
| 3 | Модель X515 | Модель X=СЖПРОБЕЛЫ(REGEXREPLACE(A3;"\d";"")) |
\d — любая цифра. Без регулярных выражений — 10 вложенных ПОДСТАВИТЬ, подробнее в статье удалить цифры.
Буквы, латиница, кириллица
Аналогично сложно удалить разом все буквы алфавита, которых 26 или 33 в случае с английскими и русскими символами соответственно. О том, как удалить латиницу в Excel, читайте в моем гайдлайне.
| A | B | C | |
|---|---|---|---|
| 1 | Текст | Без латиницы | Без кириллицы |
| 2 | чехол iPhone 15 | чехол 15=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"[A-Za-z]";"")) | iPhone 15=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"[А-Яа-яЁё]";"")) |
| 3 | кабель USB-C | кабель -=СЖПРОБЕЛЫ(REGEXREPLACE(A3;"[A-Za-z]";"")) | USB-C=СЖПРОБЕЛЫ(REGEXREPLACE(A3;"[А-Яа-яЁё]";"")) |
Квадратные скобки перечисляют диапазоны букв; Ё и ё указаны отдельно, потому что не входят в диапазон А–я. Подробнее — удалить латиницу.
Удалить всё, кроме...
Часты случаи, когда лишними считаются вообще все символы, кроме определенных. Тут речь уже больше об извлечении нужных символов, а не об удалении ненужных.
Удалить все, кроме цифр (извлечь цифры)
Номера телефонов, почтовые коды, числовые артикулы, IP адреса... Иногда проблемой является наличие в ячейках других символов, помимо цифр. Читайте об этом: Удалить всё, кроме цифр в ячейках Excel.
| A | B | |
|---|---|---|
| 1 | Текст | Только цифры |
| 2 | +7 (916) 123-45-67 | 79161234567=REGEXREPLACE(A2;"[^\d]";"") |
| 3 | индекс 101000, Москва | 101000=REGEXREPLACE(A3;"[^\d]";"") |
[^\d] — любой символ, кроме цифры. Результат — текст; два минуса перед формулой превратят его в число. Подробнее — извлечь цифры.
Удалить все, кроме букв (извлечь буквы)
Случай, когда в данных лишними являются любая пунктуация, цифры и прочие символы, кроме букв алфавита. Это могут быть:
- кириллица;
- латиница;
- любые буквы.
| A | B | |
|---|---|---|
| 1 | Текст | Только буквы |
| 2 | Иванов И.И., 1985 г.р. | Иванов ИИ гр=СЖПРОБЕЛЫ(REGEXREPLACE(A2;"[^A-Za-zА-Яа-яЁё ]";"")) |
| 3 | SKU-125 Black | SKU Black=СЖПРОБЕЛЫ(REGEXREPLACE(A3;"[^A-Za-zА-Яа-яЁё ]";"")) |
Пробел внутри квадратных скобок сохраняет границы между словами. Чтобы оставить только кириллицу, уберите из шаблона A-Za-z.
Другое
Хотите узнать, как удалять другие символы в Excel? Оставляйте комментарии под этой статьей.
Другие операции с символами в Excel
Не всегда нужны такие кардинальные меры, как удаление символов. Иногда необходимо просто обнаружить их наличие, извлечь или заменить на какие-то другие. В решении подобных задач вам помогут соответствующие разделы сайта:
Также Microsoft Excel способен на полную мощность задействовать возможности регулярных выражений. Буквы, цифры, знаки препинания, специальные символы — регулярным выражениям подвластна работа с любыми данными. Подробнее на тему читайте в статье Регулярные выражения в Excel.
Смотрите также:
- Как удалять ячейки по условию в Excel;
- Как удалять определенные слова в Excel;
- Как удалять ненужные столбцы и строки по множеству условий;
- Как убрать формулы из ячеек и оставить только значения.
Удалить символы в Excel — частые вопросы
Как удалить определённый символ во всех ячейках?
Через Ctrl+H: в поле «Найти» символ, поле «Заменить на» пустое. Или формулой =ПОДСТАВИТЬ(A2;"-";"").
Как удалить первые или последние символы?
Первые N — =ПСТР(A2;N+1;1000), последние N — =ЛЕВСИМВ(A2;ДЛСТР(A2)-N).
Как удалить всё, кроме цифр?
Регулярным выражением =REGEXREPLACE(A2;"[^\d]";"") или процедурой !SEMTools «Извлечь цифры».
Как удалить знаки препинания?
Формулой =СЖПРОБЕЛЫ(REGEXREPLACE(A2;"[^A-Za-zА-Яа-яЁё0-9 ]";"")) — подробно в статье удалить пунктуацию.
Удалить цифры, латиницу, пунктуацию, лишние пробелы или N символов с края ячейки сразу во всём диапазоне — прямо на месте, без формул — можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как удалить лишние символы в Excel
| Задача | Формула |
|---|---|
| Конкретный символ | ПОДСТАВИТЬ |
| Лишние пробелы | СЖПРОБЕЛЫ |
| Первые / последние N символов | ПСТР / ЛЕВСИМВ + ДЛСТР |
| Всё до символа | ЗАМЕНИТЬ + ПОИСК |
| Цифры, латиница, пунктуация | REGEXREPLACE с шаблоном |
Надеюсь, на этой странице вы нашли то, что искали. Для одного символа хватит ПОДСТАВИТЬ или Ctrl+H, для групп символов — регулярных выражений, а для разовой чистки больших таблиц быстрее процедуры раздела УДАЛИТЬ.
Добрый вечер! Есть ли возможность удалить все цифры и буквы, написанные в нижнем регистре и оставить только caps look слова?
Преднастроенной возможности нет, но можно извлечь слова по регулярному выражению (ознакомьтесь с их синтаксисом, открывает огромные возможности!). Выражение будет ^[A-ZА-ЯЁ]+$
Мне нужно убрать информацию массово все что после символа * Заменой это сделать не получается, тк там же при замене используется этот символ. Пример
«Ночной охотник» кон.д/ХОРЬКОВ Мясное ассорти 415гр*20
#»Ночной охотник» НАБОР ПАУЧЕЙ д/кошек 3+1 Говядина, Лосось/Судак/Тунец, Кролик и сердце в желе 85гр *6
Все что после * нужно удалить включая саму *
Звёздочка — оператор, который надо экранировать, чтобы он учитывался как настоящий символ.
Чтобы удалить все что после звездочки, включая ее, нужно в окошке замены в заменяемом выражении использовать ~**
~ (тильда) экранирует первую звездочку, а вторая звездочка обозначает любое количество символов.
Здравствуйте!
Попробовал формулу — «=ЛЕВСИМВ(A1;ДЛСТР(A1)-N)», всё получилось, но есть проблема, когда копирую полученное значение, то получаю формулу, а не то значение, которое нужно было в итоге…
Может быть есть, понимание, как получить в ячейке — не формулу, а значение?
Вам просто нужно после копирования вставлять данные как значения. Вставляйте в диапазон через меню правой кнопкой мыши, и там выбирайте «вставить как значения».
Здравствуйте. Подскажите пожалуйста, есть ли возможность из массива почт убрать строки в которых нет символа @?
Конечно, можно. Для этого достаточно воспользоваться штатной процедурой фильтрации, надстройка тут не понадобится.
Добрый день. Есть файл, в которых есть текстовые поля, и большинство из них содержит в тексте URL ссылки. Как можно их выявить и удалить из текста массово?
Проще всего это сделать с помощью регулярных выражений.
Добрый день. Из Отчета по проводкам, нужно было Фио контрагентов вытащить, допустим
Иванов Иван
Без договора
С помощью Найти и Заменить…Без договора… удалили
Но выяснилось, что осталась пустая строка
Иванов Иван
пустая строка
И теперь когда этого Контрагента включили в другие списки, то выяснилось, что…Удалить дубликаты…не работает, так как Иванов Иван и Иванов Иван с пустыми строками… воспринимаются программой как разные Контрагенты
Как можно удалить пустые строки в ячейке, вводы кажется это называется…функцией или командной?
Если знаки препинания (точки, запятые и т.д.) отсутствуют и не нужны, то самым простым способом будет воспользоваться процедурой надстройки «Удалить пунктуацию» (Удалить — символы — по типу — пунктуацию). Она удаляет все символы кроме пробелов. В том числе переносы строк.
Здравствуйте. Надо в экселе в столбце удалить символы это скобки, стоящие в строке номера символов (1,4,19,22). Через заменить не могу сделать так как в строках присутствуют другие скобки их трогать нельзя. Помогите пожалуйста . (01)02900342072704(21)5<)X%MbrdIrrT вот как выглядит код в котором надо удалить скобки (01) и (21), цифры не трогать
Вы хотите удалить скобки вместе с тем, что между ними? Тут либо регулярные выражения, либо комбинация функций ПОИСК и ЗАМЕНИТЬ. ПОИСК найдет позицию скобок, а ЗАМЕНИТЬ — удалит промежуток от открывающей до закрывающей. В общем, реализуемо.