3 лучшие формулы Excel для очистки данных продаж

Habib Benabdeslam
Habib Benabdeslam
9 мин чтения
Формулы Excel для очистки данных продаж
3 лучшие формулы Excel для очистки данных продаж
Оглавление
  1. Как извлечь домен из email-адреса
    1. Как извлечь домен из email в старой версии Excel
    2. Проверка исключений в email перед протягиванием формулы
    3. Очистка и форматирование телефонных номеров
    4. Очистка названий компаний
  2. FAQ

Используйте эти формулы Excel для очистки данных о продажах, когда перед импортом в CRM нужно извлечь домены из email, привести телефоны к единому текстовому формату и проверить названия компаний. Сохраняйте исходные столбцы, а каждый результат записывайте в новый — так вы сможете увидеть все изменения.

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

В этом руководстве разберём три задачи:

📌 Кратко для тех, кто спешит

Подготовьте столбцы с данными о продажах к проверенному импорту в CRM с помощью трёх точечных преобразований Excel. Сохраняйте исходные значения рядом с результатами и проверяйте исключения, прежде чем что-либо заменять.

  • Входные данные: email-адреса, телефоны в текстовом формате и названия компаний.
  • Результат: извлечённые домены, номера телефонов без выбранных знаков препинания и названия компаний, очищенные по явно заданному правилу удаления суффикса.

Как извлечь домен из email-адреса

Извлечь домены из списка email-адресов в Excel можно двумя способами.

Первый способ — функция TEXTAFTER, доступная в Microsoft 365, Excel для веба и Excel 2024. Проверьте совместимость своей версии в актуальной документации Microsoft.

Второй способ — сочетание функций RIGHT, LEN и FIND. Он подойдёт всем, кто не может использовать вариант с TEXTAFTER.

Чтобы извлечь домен из email-адреса, выполните следующие действия:

  1. Введите формулу =TEXTAFTER в строку формул
  2. Выберите ячейку с первым email-адресом, например B3
  3. Укажите @ в качестве искомого текста

Формула Excel будет выглядеть так:

=TEXTAFTER(B3,"@")
Домен, извлечённый из email
Домен, извлечённый из email

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

Домены, извлечённые из списка email-адресов
Домены, извлечённые из списка email-адресов

Как извлечь домен из email в старой версии Excel

Теперь разберём, как извлечь домен из email в старой версии Excel, которая не поддерживает функцию TEXTAFTER.

Будем использовать формулу =RIGHT(B3,LEN(B3)-FIND("@",B3)).

Вставьте её в строку формул и примените к ячейке с первым email-адресом.

Первый домен, извлечённый формулой =RIGHT(B3,LEN(B3)-FIND("@",B3))
Первый домен, извлечённый формулой =RIGHT(B3,LEN(B3)-FIND("@",B3))

Вот как работает эта формула: 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.

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

Формула =RIGHT(B3,LEN(B3)-FIND("@",B3)), применённая для извлечения всех доменов из списка email
Формула =RIGHT(B3,LEN(B3)-FIND("@",B3)), применённая для извлечения всех доменов из списка email

Если вам удобнее работать с 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.

Параметры форматирования в Excel
Параметры форматирования в Excel

Выберите пункт «Phone Numbers» и страну, к которой относятся номера.

Выбор формата телефонных номеров США
Выбор формата телефонных номеров США

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

Теперь закройте панель и проверьте, применил ли Excel выбранный формат.

Для этого достаточно убедиться, что в поле формата столбца отображается значение Special. На изображении ниже оно выделено.

Столбец Excel, отформатированный для телефонных номеров США
Столбец Excel, отформатированный для телефонных номеров США

На последнем этапе введите в этот столбец формулу, которая перенесёт только что очищенные номера в новый столбец, настроенный для американского формата.

В этом примере отображения проверенных номеров США формула =VALUE(D3) передаёт числовое значение в столбец. При этом D3 следует оставить без изменений в текстовом формате. Простая ссылка =D3 останется текстом, поэтому числовой формат телефона к ней применён не будет.

Простая формула для переноса телефонных номеров в новый столбец
Простая формула для переноса телефонных номеров в новый столбец

После ввода формулы нажмите ENTER, чтобы применить её.

Если всё сделано правильно, результат будет выглядеть так:

Телефонный номер США, отформатированный в Excel
Телефонный номер США, отформатированный в Excel

Затем потяните вниз маркер в правом нижнем углу ячейки, чтобы применить форматирование ко всем телефонным номерам в 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. Какой бы способ вы ни выбрали, сохраняйте исходные значения и проверяйте юридические суффиксы, аббревиатуры и неоднозначные случаи.

Часто задаваемые вопросы

Как извлечь домен из email-адреса в Excel?

В новых версиях Excel можно использовать функцию TEXTAFTER, а в старых — сочетание RIGHT, LEN и FIND.

Как лучше очистить телефонные номера в Excel?

Храните телефоны в текстовом формате, удаляйте только нужные знаки и проверяйте нули в начале, коды стран и добавочные номера. Числовое форматирование — это отдельный необязательный пример только для номеров США, а не проверка или международная нормализация.

Можно ли форматировать в Excel номера других стран?

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

Как удалить LLC, Inc. и Ltd. из названий компаний?

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

Что делать, если в Excel нет функции TEXTAFTER?

Используйте формулы, совместимые со старыми версиями Excel, например сочетание функций RIGHT, LEN и FIND, либо проверьте, поддерживает ли ваша текущая версия Excel функцию TEXTAFTER.