Как удалить лишние символы в Excel: оставить только цифры и буквы

Типичная боль: в Excel прилетели телефоны, артикулы или номера счетов, а там всё вперемешку — скобки, тире, пробелы, «№», плюсики, слэши. Формулы ломаются, сводные считают не то, VLOOKUP/ВПР не находит совпадения.

Хочется простого результата: убрать лишние видимые знаки и оставить только то, что действительно должно храниться в коде, артикуле, номере или тексте.

Зачем вообще удалять лишние символы

Смотри, как выглядит реальная «грязная» таблица:

Проблемы от такого «зоопарка» символов:

• формулы сравнения считают «разные» значения, хотя по сути это один и тот же номер;
• поиск по телефону или артикулу не срабатывает;
• сводные таблицы дробят один код на несколько разных строк;
• отчёты выглядят хаотично и вызывают недоверие.

Поэтому задача не в том, чтобы бездумно стереть всё «лишнее», а в том, чтобы привести строку к выбранному стандарту: оставить нужные буквы, цифры и разделители, а подтверждённый мусор убрать.

Общая логика решения

Мы пойдём от простого к более универсальному:

1. Быстрый способ: Ctrl+H — убрать пару конкретных символов (скобки, тире, пробелы).
2. Формульный подход: ПОДСТАВИТЬ и функции по позиции — когда мусор предсказуем или структура строки стабильна.
3. Повторяемая очистка в Power Query: Replace Values или Text.Select — только после того, как список разрешённых символов понятен.

Не переживай, если сначала кажется сложно. Главное — разделить два подхода: ПОДСТАВИТЬ удаляет или заменяет найденное совпадение, а ЗАМЕНИТЬ, ЛЕВСИМВ, ПРАВСИМВ, ПСТР и ДЛСТР помогают работать с позицией и длиной строки.

Способ 1. Быстрое удаление символов через Ctrl+H

Этот вариант подойдёт, если лишних видимых символов мало и они предсказуемы: скобки, дефисы, пробелы, «№». Перед удалением проверь, что знак не является частью правила хранения: например, плюс в международном телефоне или дефис в артикуле могут быть значимыми.

Пример таблицы

Шаги

1. Выдели только тот столбец или диапазон, где нужно убрать подтверждённый лишний символ. Для важных данных сначала сделай копию.
2. Нажми Ctrl + H (Найти и заменить).
3. В поле «Найти» введи конкретный символ, который нужно убрать или заменить.
4. Если знак нужно удалить, оставь поле «Заменить на» пустым. Если знак должен стать разделителем, введи обычный пробел или другой нужный символ.
5. Сначала нажми «Найти далее» и проверь найденную ячейку.
6. Только после проверки нажми «Заменить все» для выбранной области.

Такой подход экономит время, когда формат более-менее одинаковый и мусора немного. Но массовая замена затрагивает все совпадения в выбранной области, поэтому её нельзя запускать по всему листу без проверки.

После удаления видимых лишних знаков стоит проверить таблицу шире: типы данных, пробелы, пустые строки, категории и дубликаты. Для этого подходит полный чек-лист очистки таблицы перед анализом.

Способ 2. Формулы ПОДСТАВИТЬ и удаление по позиции

Если набор мусорных символов известен (скобки, дефисы, «№», «доб.» и т.п.), удобнее вынести очистку в формулу. Такой подход лучше масштабируется и прозрачен: всегда видно, что именно мы убираем.

Таблица «до/после»

Удаление по совпадению и по позиции

ПОДСТАВИТЬ ищет конкретный текст и удаляет все его совпадения, если третьим аргументом указана пустая строка. Например, =ПОДСТАВИТЬ(A2;"-";"") уберёт все дефисы в A2. Если нужен разделитель, используй замену на пробел: =ПОДСТАВИТЬ(A2;"-";" ").

ЗАМЕНИТЬ работает иначе: она удаляет или меняет фрагмент по позиции. В формуле =ЗАМЕНИТЬ(A2;1;2;"") второй аргумент — начальная позиция, третий — количество символов. Такой способ безопасен только при стабильной структуре строк.

• Удалить первые 2 символа: =ПРАВСИМВ(A2;ДЛСТР(A2)-2). Перед применением проверь, что строки длиннее двух символов и префикс действительно лишний.
• Удалить последний символ: =ЛЕВСИМВ(A2;ДЛСТР(A2)-1). На пустых ячейках и строках длиной 1 нужна отдельная проверка.
• Извлечь середину: =ПСТР(A2;3;5). Это не универсальная очистка, а извлечение фрагмента из строки с постоянной структурой.

Минус этого подхода в том, что он всё равно не универсален:

• нужно перечислить каждый мусорный символ;
• если появится новый знак, формулу придётся дописывать;
• сложно сделать одну формулу «на все случаи».

Если правил становится много или очистка повторяется каждый месяц, удобнее перейти в Power Query.

Способ 3. Повторяемая очистка в Power Query

Power Query — это встроенный в Excel инструмент для загрузки и очистки данных. Для известных знаков можно использовать Заменить значения, а для строгого списка разрешённых символов — Text.Select. Это не универсальная кнопка «починить всё»: правило нужно выбирать под конкретный тип данных.

Шаг 1. Загрузить таблицу в Power Query

1. Выдели таблицу с данными (желательно, чтобы это была «умная таблица» Ctrl+T).
2. Перейди на вкладку Данные → Из таблицы/диапазона.
3. Подтверди диапазон и нажми ОК — откроется редактор Power Query.

Допустим, столбец с исходными значениями называется [Исходный_код].

Шаг 2. Создать новый очищенный столбец

1. Во вкладке Добавление столбца выбери Пользовательский столбец.
2. Дай новое имя, например «Очищенный_код».
3. В поле формулы вставь нужный вариант Text.Select (см. ниже).
4. Нажми ОК, проверь результат.
5. Когда всё устраивает — нажми Закрыть и загрузить, чтобы вернуть данные в Excel.

Вариант 1. Оставить только цифры

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

Text.Select([Исходный_код], {"0".."9"})

Вариант 2. Оставить только буквы

Подходит, когда нужно вытащить только буквенную часть кода: префиксы, суффиксы, литеры. Включим русские и латинские буквы.

Text.Select(
    [Исходный_код],
    {"А".."Я", "а".."я", "A".."Z", "a".."z", "Ё", "ё"}
)

Вариант 3. Цифры + буквы + пробелы

Иногда нужно удалить только спецсимволы, а цифры, буквы и пробелы оставить. Например, почистить наименования товаров от лишних знаков, но не трогать текст.

Text.Select(
    [Исходный_код],
    {"0".."9", "А".."Я", "а".."я", "A".."Z", "a".."z", "Ё", "ё", " "}
)

Типичные ошибки новичков

• Чистят данные прямо в исходном столбце.
  Потом сложно понять, что сломалось, и невозможно откатиться. Всегда работай с копией столбца или с результатом Power Query.
• Смешивают числа и текстовый формат.
  Очищенные коды лучше хранить как текст, чтобы Excel не обрезал ведущие нули и не переводил длинные номера в формат 1,23E+11.
• Пытаются одной формулой решить «всё и сразу».
  В чистом Excel универсальные формулы получаются длинными и хрупкими. Если задача повторяется — выгоднее перенести очистку в Power Query.
• Забывают про невидимые символы.
  Даже после Ctrl+H в ячейке могут сидеть неразрывные пробелы и скрытые знаки. Для таких случаев нужна отдельная диагностика невидимых символов, а не обычное удаление видимого знака.
• Слишком рано усложняют себе жизнь.
  Иногда достаточно пары замен через Ctrl + H или функции ПОДСТАВИТЬ, чтобы привести данные в порядок. Не обязательно сразу лезть в сложные конструкции.

Итоги

• Лишние символы в кодах и номерах ломают формулы, поиск и сводные.
• Для простых случаев достаточно Ctrl + H или формулы ПОДСТАВИТЬ; при стабильной структуре можно использовать функции, работающие по позиции.
• Power Query помогает повторять очистку, но правило нужно выбирать осознанно: Replace Values для известных знаков, Text.Select — для строгого списка разрешённых символов.
• Отдельно обрабатываем удаление по совпадению, удаление по позиции и строгий отбор разрешённых символов.
• Храним очищенные коды как текст и всегда работаем с копией данных.

Такой подход экономит время и нервы: данные приходят «грязными», а уходят в отчёты уже аккуратными и предсказуемыми.

Что дальше

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

Связанные материалы

• Как заменить символы в Excel — массовая замена через Ctrl+H и формулы ПОДСТАВИТЬ/ЗАМЕНИТЬ.
• Как очистить телефоны от мусора в Excel — отдельный разбор телефонов: скобки, +7, пробелы.
• 7 способов обработать список в Excel — большой обзор приёмов по очистке и подготовке списков.