Используйте эти формулы Excel для очистки данных о продажах, когда перед импортом в CRM нужно извлечь домены из email, привести телефоны к единому текстовому формату и проверить названия компаний. Сохраняйте исходные столбцы, а каждый результат записывайте в новый — так вы сможете увидеть все изменения.
Начнём с нескольких проблемных строк: номера телефона с нулём в начале, email без разделителя и названия компании со словом, похожим на юридический суффикс. Формула, которая правильно работает на первой аккуратной строке, после протягивания может повредить такие данные.
В этом руководстве разберём три задачи:
- Извлечение доменов из email-адресов.
- Очистка телефонных номеров от знаков препинания с сохранением исходных цифр.
- Удаление проверенного юридического суффикса без удаления слов внутри названия.
📌 Кратко для тех, кто спешит
Подготовьте столбцы с данными о продажах к проверенному импорту в CRM с помощью трёх точечных преобразований Excel. Сохраняйте исходные значения рядом с результатами и проверяйте исключения, прежде чем что-либо заменять.
- Входные данные: email-адреса, телефоны в текстовом формате и названия компаний.
- Результат: извлечённые домены, номера телефонов без выбранных знаков препинания и названия компаний, очищенные по явно заданному правилу удаления суффикса.
Как извлечь домен из email-адреса
Извлечь домены из списка email-адресов в Excel можно двумя способами.
Первый способ — функция TEXTAFTER, доступная в Microsoft 365, Excel для веба и Excel 2024. Проверьте совместимость своей версии в актуальной документации Microsoft.
Второй способ — сочетание функций RIGHT, LEN и FIND. Он подойдёт всем, кто не может использовать вариант с TEXTAFTER.
Чтобы извлечь домен из email-адреса, выполните следующие действия:
- Введите формулу
=TEXTAFTERв строку формул - Выберите ячейку с первым email-адресом, например B3
- Укажите
@в качестве искомого текста
Формула Excel будет выглядеть так:
=TEXTAFTER(B3,"@")
Чтобы применить формулу ко всем email-адресам, потяните вниз маркер в правом нижнем углу ячейки с исходной формулой.
Как извлечь домен из email в старой версии Excel
Теперь разберём, как извлечь домен из email в старой версии Excel, которая не поддерживает функцию TEXTAFTER.
Будем использовать формулу =RIGHT(B3,LEN(B3)-FIND("@",B3)).
Вставьте её в строку формул и примените к ячейке с первым email-адресом.
Вот как работает эта формула: RIGHT берёт символы с конца email-адреса, а FIND и LEN вместе определяют, сколько именно символов нужно извлечь.
Рассмотрим адрес john.doe123@abc.com: всего в нём 19 символов.
LEN(B3) возвращает 19 символов.
FIND ищет символ @ в john.doe123@abc.com и сообщает, что он находится на 12-й позиции:
FIND("@",B3) возвращает 12.
Вычитаем 12 из 19 и передаём получившееся число 7 в функцию RIGHT.
Так Excel извлекает последние 7 символов и возвращает результат abc.com.
Чтобы применить формулу ко всем ячейкам, потяните вниз маркер в правом нижнем углу ячейки с исходной формулой.
Если вам удобнее работать с CSV, вы можете извлечь домены в Datablist, а затем проверить результаты рядом с исходным столбцом.
Проверка исключений в email перед протягиванием формулы
TEXTAFTER(B3,"@") возвращает ошибку, если разделитель отсутствует. Для предварительной проверки можно использовать формулу =IFERROR(LOWER(TRIM(TEXTAFTER(B3,"@"))),"Проверить email"). Она лишь извлекает текст и не подтверждает, что адрес существует или на него можно доставить письмо. Строки с несколькими адресами или несколькими символами @ следует отклонять, а не считать извлечённую часть надёжным доменом компании.
Например, для alex@example.com результатом будет example.com, а alex.example.com потребует проверки. Храните полный email рядом с доменом, чтобы случайно не объединить двух сотрудников одной компании в один контакт.
Очистка и форматирование телефонных номеров
Теперь очистим и отформатируем телефонные номера в два этапа.
Сначала очистим номера с помощью следующей формулы:
=TEXTJOIN("",TRUE,TEXTSPLIT(B3,{"(",")","-"," ","."},,TRUE))
Она удаляет только перечисленные знаки и возвращает текст. Не добавляйте +0 и не преобразовывайте результат в число другим способом: иначе исчезнут нули в начале номера и международный знак +. Исходный столбец с телефонами также импортируйте как текст — формула не сможет восстановить нули, уже удалённые Excel. Microsoft объясняет это поведение в руководстве по сохранению ведущих нулей.
Функция TEXTSPLIT доступна только в поддерживаемых версиях Excel. В примере используются английские названия функций и запятые в качестве разделителей аргументов; в локализованной версии названия и разделители могут отличаться. Формула не проверяет коды стран, добавочные номера и доступность телефона.
Вот как выглядит результат применения формулы.
Примените формулу ко всем номерам, потянув вниз маркер в правом нижнем углу исходной ячейки, как показано ниже.
Например, текст 020 7946 0958 превратится в 02079460958, а +44 (20) 7946-0958 — в +442079460958. Добавочные номера храните отдельно, а строки с буквами или неожиданными символами отправляйте на проверку. Телефон — это идентификатор, а не число для вычислений.
На следующих скриншотах показан необязательный формат отображения номеров США. Используйте его только для проверенных десятизначных американских номеров и в отдельном столбце. Для международного экспорта и импорта в CRM сохраняйте очищенный текст: этот способ отображения не нормализует международные номера.
Создайте отдельный столбец с названием Отформатированные номера телефонов.
Выберите ячейку под заголовком и нажмите сочетание клавиш Excel Command + 1, чтобы открыть все параметры форматирования.
Выберите формат Special.
Выберите пункт «Phone Numbers» и страну, к которой относятся номера.
💡 Форматы чисел в Excel меняют только внешний вид числовых ячеек. Они не добавляют отсутствующий код страны и не проверяют корректность номера. Для международного списка сохраняйте значения в текстовом виде и проверяйте данные о стране, прежде чем выбирать отдельный процесс очистки телефонов.
Теперь закройте панель и проверьте, применил ли Excel выбранный формат.
Для этого достаточно убедиться, что в поле формата столбца отображается значение Special. На изображении ниже оно выделено.
На последнем этапе введите в этот столбец формулу, которая перенесёт только что очищенные номера в новый столбец, настроенный для американского формата.
В этом примере отображения проверенных номеров США формула =VALUE(D3) передаёт числовое значение в столбец. При этом D3 следует оставить без изменений в текстовом формате. Простая ссылка =D3 останется текстом, поэтому числовой формат телефона к ней применён не будет.
После ввода формулы нажмите ENTER, чтобы применить её.
Если всё сделано правильно, результат будет выглядеть так:
Затем потяните вниз маркер в правом нижнем углу ячейки, чтобы применить форматирование ко всем телефонным номерам в Excel:
Очистка названий компаний
Очистка названий компаний — один из важнейших этапов подготовки к Outbound-кампаниям, поэтому его нельзя было пропустить в этом руководстве.
Используйте правило удаления суффикса, которое можно однозначно объяснить. Функция SUBSTITUTE заменяет совпадающий текст в любом месте строки. Например, =SUBSTITUTE("Acme Consulting"," Co","") вернёт Acmensulting, удалив часть настоящего названия. Регистр имеет значение: если использовать " co", название Acme Consulting останется без изменений, если только предварительно не перевести исходный текст в нижний регистр. Результат цепочки замен также зависит от их порядка. Не стоит применять такие общие цепочки замен к столбцу CRM.
Если утверждённое правило для вашего списка звучит как удалить стоящий в конце пробел и суффикс LLC, используйте формулу:
=IF(RIGHT(TRIM(B4),4)=" LLC",TRIM(LEFT(TRIM(B4),LEN(TRIM(B4))-4)),TRIM(B4))
Это намеренно узкое правило. Acme LLC превратится в Acme, а Acme LLC Services и Acme Consulting останутся без изменений. Оно не учитывает L.L.C., все юридические формы и особенности разных стран. При необходимости добавляйте отдельно проверенные правила для своих данных.
На скриншотах ниже показан старый пример с широкой заменой. Для итогового результата используйте точное правило выше и сравнивайте изменённое название с исходным, прежде чем принимать замену.
Как работает более точная формула: TRIM удаляет обычные пробелы по краям, RIGHT проверяет последние четыре символа, а LEFT удаляет только подтверждённое окончание. В стандартных формулах Excel сравнение выполняется без учёта регистра. Не рассчитывайте, что TRIM удалит все виды пробельных символов Unicode.
Вот как выглядит результат применения формулы очистки названия компании:
Не забудьте потянуть вниз маркер в правом нижнем углу ячейки с исходной формулой, чтобы применить её ко всем названиям компаний:
Остаётся ещё одна проблема: названия компаний не приведены к нужному регистру.
Используйте =PROPER(D4) только тогда, когда каждое слово действительно должно начинаться с заглавной буквы. Эта функция превращает IBM в Ibm, поэтому при необходимости сохраняйте официальное написание брендов или добавляйте явные исключения. Скриншоты демонстрируют преобразование, но не гарантируют правильное написание коммерческого названия.
Вот как выглядит правильный результат:
В завершение потяните вниз маркер в правом нижнем углу, чтобы применить формулу ко всему списку названий компаний.
Если у вас уже есть утверждённый список старых и новых названий, воспользуйтесь процессом замены нескольких значений, чтобы применить эти соответствия.
Для более разнородных названий подойдёт руководство по очистке названий компаний с помощью AI. Какой бы способ вы ни выбрали, сохраняйте исходные значения и проверяйте юридические суффиксы, аббревиатуры и неоднозначные случаи.




















