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

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

Хочется простого результата: оставить только цифры и буквы, а весь мусор аккуратно убрать.

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

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

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

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

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

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

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

1. Быстрый способ: Ctrl+H — убрать пару конкретных символов (скобки, тире, пробелы).
2. Формульный подход: ПОДСТАВИТЬ и СЖПРОБЕЛЫ — когда мусор предсказуем.
3. Универсальный метод в Power Query: Text.Select — оставить только нужные символы по списку.

Не переживай, если сначала кажется сложно. Главное — понять идею: мы не «ищем мусор», а выбираем, что оставить.

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

Этот вариант подойдёт, если лишних символов мало и они предсказуемы: скобки, дефисы, пробелы, «+», «№».

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

Шаги

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

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

Способ 2. Формулы ПОДСТАВИТЬ + СЖПРОБЕЛЫ

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

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

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

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

Поэтому, если ты хочешь по-настоящему универсальный метод «оставить только цифры/буквы», удобнее перейти в Power Query.

Способ 3. Универсальный метод в Power Query: Text.Select

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 в ячейке могут сидеть неразрывные пробелы и скрытые знаки. Для таких случаев полезна отдельная диагностика — статья «Как удалить невидимые символы в Excel».
• Слишком рано усложняют себе жизнь.
  Иногда достаточно пары замен через Ctrl+H и функции СЖПРОБЕЛЫ, чтобы привести данные в порядок. Не обязательно сразу лезть в сложные конструкции.

Итоги

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

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

Что дальше

Хочешь продолжить и спокойно чувствовать себя в Excel, даже когда данные приезжают «кривыми»? Посмотри бесплатный курс:

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

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