Приведение базы контактов к единому формату: телефоны, email и названия компаний — пошаговая нормализация в Excel для CRM

Что ломается без нормализации:

• Дубли в CRM: каждая вариация телефона или email создаёт отдельный контакт;
• Сломанная сегментация: фильтр «компании из Москвы» не найдёт «г. Москва» и «Москва, ул. Тверская» в одной выдаче;
• Провал рассылки: письмо на email с пробелом или кавычкой уходит в никуда;
• Искажённая аналитика: количество «уникальных» лидов завышено в 1,5–2 раза.

Шаг 1. Удалить всё, кроме цифр. Функция ПОДСТАВИТЬ убирает конкретные символы, но когда их десяток вариантов, удобнее формула с массивом:

=СЦЕП(ЕСЛИ(ЕЧИСЛО(--ПСТР(A2;СТРОКА($1:$50);1));ПСТР(A2;СТРОКА($1:$50);1);""))

Формула массива (вводится через Ctrl+Shift+Enter в старых версиях Excel). Результат для +7 (999) 123-45-67 → 79991234567.

Шаг 2. Привести код страны к +7. После очистки возможны варианты: 7999..., 8999..., 999.... Приводим к единому префиксу:

=ЕСЛИ(ЛЕВСИМВ(B2;1)="8"; "+7"&ПСТР(B2;2;10);
   ЕСЛИ(ЛЕВСИМВ(B2;1)="7"; "+"&B2;
   ЕСЛИ(ДЛСТР(B2)=10; "+7"&B2;
   "НЕКОРРЕКТНЫЙ")))

Где B2 — ячейка с очищенными цифрами. Формула обрабатывает три случая: номер через 8, номер уже с 7, и номер без кода страны (10 цифр).

Шаг 3. Проверить длину. Корректный российский номер — ровно 11 цифр после + (12 символов с плюсом):

=ЕСЛИ(ДЛСТР(C2)<>12; "ОШИБКА ДЛИНЫ"; C2)

Номера короче 11 цифр помечаются — возможно, это городской телефон без кода или опечатка.

Шаг 1. Удалить пробелы и обрезать края. Функция СЖПРОБЕЛЫ убирает лишние пробелы внутри строки, а СЖПРОБЕЛЫ + проверка крайних символов решает основную проблему:

=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);" ";"")

Затем убираем точку или запятую в конце:

=ЕСЛИ(ПРАВСИМВ(B2;1)="."; ЛЕВСИМВ(B2;ДЛСТР(B2)-1);
   ЕСЛИ(ПРАВСИМВ(B2;1)=","; ЛЕВСИМВ(B2;ДЛСТР(B2)-1);
   B2))

Шаг 2. Проверить структуру. Email должен содержать ровно один символ @ и точку после него:

=ЕСЛИ(И(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;"@";""))=1;
        НАЙТИ(".";A2;НАЙТИ("@";A2))>0);
        A2; "НЕВАЛИДНЫЙ")

Адреса с двумя @ или без точки после собаки маркируются как невалидные и требуют ручной проверки.

Шаг 3. Привести к нижнему регистру. По стандарту локальная часть email регистрозависима, но большинство почтовых систем игнорируют регистр. Для единообразия в CRM — приводим к нижнему:

=СТРОЧН(C2)

Шаг 1. Замена длинной формы ОПФ. Создаём таблицу подстановок на отдельном листе и используем ПОДСТАВИТЬ:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;
        "ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ";"ООО");
        "АКЦИОНЕРНОЕ ОБЩЕСТВО";"АО");
        "ПУБЛИЧНОЕ АКЦИОНЕРНОЕ ОБЩЕСТВО";"ПАО")

Шаг 2. Привести кавычки. Заменяем все варианты кавычек на ёлочки «»:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;
        """";"«";1); """";"»";1);
        "'";"«")

Важно: заменяем первую и вторую двойную кавычку отдельно (на « и » соответственно).

Шаг 3. Регистр. Название компании приводим к формату «первая буква заглавная»:

=ПРОПНАЧ(C2)

После этой операции ООО «РОМАШКА» превратится в Ооо «Ромашка». Потребуется ручная правка аббревиатур (ООО, АО, ЗАО) — но это быстрее, чем править регистр вручную для всех ячеек.

Решение — справочник синонимов на отдельном листе. Колонка A: все возможные написания города; колонка B: каноническое название. Формула для подстановки:

=ВПР(СЖПРОБЕЛЫ(A2); Справочник!A:B; 2; ЛОЖЬ)

Если город не найден в справочнике, формула вернёт #Н/Д — это сигнал добавить новый вариант в справочник.

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

Вариант 1 — Power Query (рекомендуется). Создайте запрос, который:

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

Преимущество: при добавлении нового файла в папку достаточно нажать «Обновить» — все шаги применятся автоматически. Power Query доступен в Excel 2016 и новее (вкладка «Данные» → «Получить данные»).

Вариант 2 — VBA-макрос. Если нужна максимальная гибкость (сложная логика подстановок, обращение к внешним API для валидации), пишем макрос. Шаблон:

Sub NormalizeContacts()
    Dim ws As Worksheet, lastRow As Long, i As Long
    Set ws = ThisWorkbook.Sheets("Контакты")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        ' очистка телефона
        ws.Cells(i, 3).Value = CleanPhone(ws.Cells(i, 3).Value)
        ' очистка email
        ws.Cells(i, 4).Value = CleanEmail(ws.Cells(i, 4).Value)
    Next i
End Sub

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

1. Контрольная сумма строк. Количество строк до и после очистки должно совпадать. Если стало меньше — вы случайно удалили записи (например, при удалении дубликатов не проверили диапазон).

2. Подсчёт валидных/невалидных. Добавьте сводную строку внизу таблицы:

=СЧЁТЕСЛИ(D2:D501;"НЕВАЛИДНЫЙ")

Если невалидных больше 5–10% — исходные данные требуют доочистки. Возможно, часть контактов была собрана с ошибками и их лучше исключить из импорта.

3. Выборочная ручная проверка. Возьмите 10–15 случайных строк и сравните исходное значение с очищенным. Откройте пару сайтов компаний и сверьте телефон/email по официальным контактам. Это займёт 5 минут, но обнаружит системные ошибки формул.