Как очистить телефоны от мусора (скобки, +7, пробелы) в Excel

Классика: выгружаешь телефоны из CRM — и в одном столбце живут «+7 (999) 123-45-67», «8 999 123 45 67», «7-999-123-45-67», «89991234567». Визуально понятно, что это одни и те же номера. Но Excel так не считает.

Формулы не находят совпадения, дубликаты «не ловятся», телефония ругается на формат, а менеджеры руками дописывают цифры. Такая каша в номерах — одна из самых неприятных проблем в реальных таблицах.

Не переживай, если раньше ты просто правил телефоны вручную. В этой статье разберём, как очистить телефоны от мусора в Excel: убрать оформление, проверить длину и привести номера к выбранному стандарту хранения.

Проблема: один и тот же номер в десятке разных видов

Когда телефоны приходят из разных источников (сайты, формы, CRM, ручной ввод), в столбце обычно смешано всё:

Для глаз это один и тот же номер клиента. Для Excel — совершенно разные строки. В сложных таблицах это ломает:

• поиск дубликатов;
• сравнение баз между собой;
• выгрузку в телефонию или рассылки;
• аналитику по клиентам (сколько уникальных номеров и т.п.).

Решение — сначала очистить телефоны от мусора, а уже потом удалять дубликаты, сравнивать и выгружать.

Решение: сначала выбрать стандарт хранения

Есть много вариантов оформления телефона. В этой статье основной рабочий пример — российский номер в формате 11 цифр без пробелов и знаков: 79991234567. Это не универсальный формат для всех данных, а один выбранный стандарт. Если в базе есть международные номера, добавочные или требование хранить плюс, правило нужно адаптировать до очистки.

Выбранный стандарт экономит время, потому что с ним легко:

• искать дубликаты и совпадения;
• сравнивать списки с помощью формул;
• загружать телефоны в CRM и телефонию.

Шаг 1. Быстрая очистка через «Найти и заменить»

Если таблица не слишком сложная, часть мусора можно убрать за пару минут с помощью Ctrl + H.

Что обычно нужно убрать

• пробелы: " ";
• скобки: "(" и ")";
• дефисы и длинные тире: "-", "—";
• плюс перед кодом страны: "+", только если выбран стандарт без плюса;
• служебные подписи вроде «тел.» или «моб.»; добавочные номера лучше выносить отдельно.

Пошагово

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

Шаг 2. Аккуратно обрабатываем «+7» и «8»

После очистки от скобок и пробелов в столбце могут остаться номера вида:

Здесь важно решить, к какому виду ты хочешь прийти. Например:

• оставить всё в формате 7xxxxxxxxxx;
• или всё в формате 8xxxxxxxxxx;
• или хранить международный формат с плюсом, например +79991234567.

Приводим «+7…» к «7…» через замену

Если выбран стандарт без плюса, простой вариант — удалить только сам знак +. Для международной базы это правило применять нельзя автоматически: плюс может быть значимым признаком формата.

1. Выдели столбец с телефонами.
2. Нажми Ctrl + H.
3. В поле «Найти» введи +, в «Заменить на» оставь пусто.
4. Сначала нажми «Найти далее» и проверь пример, затем используй «Заменить все» для выбранной области.

В итоге +79991234567 превратится в 79991234567, если именно такой стандарт выбран заранее.

Шаг 3. Формулами очищаем оформление

Если мусора много, удобнее использовать формулы, которые удаляют известные символы оформления. Это ещё не нормализация страны и первой цифры — только подготовка строки к проверке.

Один из базовых приёмов — поэтапно заменять каждый ненужный символ в формуле ПОДСТАВИТЬ. Предположим, исходный телефон в ячейке A2.

Поэтапная очистка во вспомогательных столбцах

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

Шаг 4. Проверяем длину номера и приводим к единому виду

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

Пусть очищенный номер лежит в G2.

Проверяем длину номера

=ДЛСТР(G2)

• если результат 11 — длина соответствует выбранному российскому стандарту, но сам номер всё равно нужно проверить;
• если 10 — код страны можно добавить только если база гарантированно российская;
• если больше или меньше — номер нужно проверить вручную.

Добавляем код «7» к 10-значным номерам

Если в G2 лежит «9991234567», а мы хотим «79991234567»:

=ЕСЛИ(ДЛСТР(G2)=10; "7"&G2; G2)

То есть для 10-значных номеров добавляем «7» слева, остальные оставляем как есть.

Заменяем ведущую «8» на «7»

Если российские номера нужно привести к формату «7xxxxxxxxxx»:

=ЕСЛИ(И(ДЛСТР(G2)=11; ЛЕВСИМВ(G2;1)="8"); "7"&ПРАВСИМВ(G2;10); G2)

Такая формула:

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

Power Query для регулярных выгрузок

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

if [Телефон] = null then null else Text.Select(Text.From([Телефон]), {"0".."9"})

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

Пример «до / после» для списка телефонов

Полезные материалы по очистке списков

Если ты часто работаешь с «грязными» данными, пригодятся и другие приёмы:

• Как убрать лишние пробелы в Excel — если обычная очистка не убирает пробельный мусор.
• Как удалить дубликаты в Excel — после нормализации телефоны можно сравнивать, но телефон не всегда достаточный ключ дубля.

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

• Чистят телефоны прямо в исходном столбце. Потом сложно откатиться и проверить исходные данные.
• Смешивают текст и числа. Превращают телефоны в числа, теряют ведущие нули и плюсы.
• Убирают «+7», но не приводят остальную часть к единому виду. В результате номера формально разные.
• Удаляют скобки и пробелы, но забывают про дефисы и «доб.». Формулы сравнения всё равно считают телефоны разными.
• Не проверяют длину номера. В списке остаются обрезанные или лишние цифры.

Итоги

Очистка телефонов в Excel — это не «магия», а понятный алгоритм: убрать лишние символы, аккуратно обработать код страны, проверить длину и привести номер к выбранному стандарту.

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

Смотри на телефоны как на данные, а не только как на текст — и Excel быстро станет твоим союзником, а не источником странных ошибок.

Что дальше

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