Процедура доступна в !SEMTools Lite и Pro.
Извлечь e-mail из текста ячейки в Excel нужно всякий раз, когда почта лежит не в отдельном столбце, а внутри фразы: «Иван Петров ivan.petrov@mail.ru», «Пишите: sales@semtools.guru». В Excel 365 это делает одна формула с регулярным выражением: =РЕГИЗВЛЕЧЬ(A2;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"). В более ранних версиях придётся собрать конструкцию из пяти функций, а без формул весь столбец обрабатывает процедура !SEMTools в 2 клика.
Если нужно не забрать почту, а, наоборот, найти или удалить строки, где она есть, смотрите статью про удаление ячеек по содержимому.
Когда нужно вытащить почту из текста
Почти всегда это данные, которые человек вводил в свободной форме или которые пришли из чужой системы. Типичные случаи:
- Выгрузка CRM или формы заявок — «Иванов, тел. 8 916…, почта ivan@mail.ru» в одном поле.
- Контакты, собранные с сайтов, — e-mail вперемешку с адресом и часами работы. Как такие данные получают, рассказано в статье про парсинг сайтов в Excel.
- Подписи писем и комментарии — «e-mail: hr@company.com», «admin@site.org — администратор».
- Списки рассылки, где в ячейке сразу несколько адресов через запятую.
Извлечь e-mail формулой в Excel 365
Функция РЕГИЗВЛЕЧЬ возвращает часть текста, подходящую под шаблон. Шаблон адреса почты: буквы, цифры и символы ._%+- до «@», затем домен и зона из двух и более латинских букв.
| A | B | C | |
|---|---|---|---|
| 1 | Текст | Первый адрес | Все адреса |
| 2 | Иван Петров ivan.petrov@mail.ru | ivan.petrov@mail.ru=ЕСЛИОШИБКА(РЕГИЗВЛЕЧЬ(A2;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}");"") | ivan.petrov@mail.ru=ЕСЛИОШИБКА(ОБЪЕДИНИТЬ(", ";ИСТИНА;РЕГИЗВЛЕЧЬ(A2;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}";1));"") |
| 3 | info@firma.ru, buh@firma.ru | info@firma.ru=ЕСЛИОШИБКА(РЕГИЗВЛЕЧЬ(A3;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}");"") | info@firma.ru, buh@firma.ru=ЕСЛИОШИБКА(ОБЪЕДИНИТЬ(", ";ИСТИНА;РЕГИЗВЛЕЧЬ(A3;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}";1));"") |
| 4 | anna_smirnova@yandex.ru (Анна) | anna_smirnova@yandex.ru=ЕСЛИОШИБКА(РЕГИЗВЛЕЧЬ(A4;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}");"") | anna_smirnova@yandex.ru=ЕСЛИОШИБКА(ОБЪЕДИНИТЬ(", ";ИСТИНА;РЕГИЗВЛЕЧЬ(A4;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}";1));"") |
| 5 | Тел. 8 916 555-12-34 | =ЕСЛИОШИБКА(РЕГИЗВЛЕЧЬ(A5;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}");"") | =ЕСЛИОШИБКА(ОБЪЕДИНИТЬ(", ";ИСТИНА;РЕГИЗВЛЕЧЬ(A5;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}";1));"") |
| 6 | dmitry+test@outlook.com | dmitry+test@outlook.com=ЕСЛИОШИБКА(РЕГИЗВЛЕЧЬ(A6;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}");"") | dmitry+test@outlook.com=ЕСЛИОШИБКА(ОБЪЕДИНИТЬ(", ";ИСТИНА;РЕГИЗВЛЕЧЬ(A6;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}";1));"") |
Третий аргумент 1 в столбце C заставляет РЕГИЗВЛЕЧЬ вернуть все совпадения, а ОБЪЕДИНИТЬ склеивает их через запятую. ЕСЛИОШИБКА оставляет ячейку пустой, если почты нет: без неё было бы #Н/Д.
В английском Excel: =IFERROR(REGEXEXTRACT(A2,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"),"")
Скобки, запятые и тире вокруг адреса в результат не попадают: шаблон берёт только допустимые для почты символы. Регистр не важен, «Ivan@Mail.ru» найдётся так же.
Адреса на кириллических доменах (zakaz@мебель.рф) этот шаблон не видит. Если они встречаются в ваших данных, добавьте русские буквы в часть после «@»:
=РЕГИЗВЛЕЧЬ(A2;"[A-Za-z0-9._%+-]+@[A-Za-zА-Яа-яЁё0-9.-]+\.[A-Za-zА-Яа-яЁё]{2,}")
Формула для Excel 2019 и более ранних версий
Без регулярных выражений e-mail достают классическим приёмом: обрезают текст по первому пробелу после «@», а из остатка берут последнее слово.
| A | B | |
|---|---|---|
| 1 | Текст | Адрес |
| 2 | Иван Петров ivan.petrov@mail.ru | ivan.petrov@mail.ru=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(ЛЕВСИМВ(A2;ПОИСК(" ";A2&" ";ПОИСК("@";A2))-1);" ";ПОВТОР(" ";100));100));"") |
| 3 | Пишите: sales@semtools.guru | sales@semtools.guru=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(ЛЕВСИМВ(A3;ПОИСК(" ";A3&" ";ПОИСК("@";A3))-1);" ";ПОВТОР(" ";100));100));"") |
| 4 | Почта: o.volkova@gmail.com, офис | o.volkova@gmail.com,=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(ЛЕВСИМВ(A4;ПОИСК(" ";A4&" ";ПОИСК("@";A4))-1);" ";ПОВТОР(" ";100));100));"") |
| 5 | нет почты | =ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(ЛЕВСИМВ(A5;ПОИСК(" ";A5&" ";ПОИСК("@";A5))-1);" ";ПОВТОР(" ";100));100));"") |
| 6 | admin@site.org — администратор | admin@site.org=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(ЛЕВСИМВ(A6;ПОИСК(" ";A6&" ";ПОИСК("@";A6))-1);" ";ПОВТОР(" ";100));100));"") |
ПОИСК("@") находит собаку, следующий ПОИСК — первый пробел после неё. ЛЕВСИМВ отрезает всё правее, ПОДСТАВИТЬ раздувает пробелы до 100 символов, ПРАВСИМВ берёт последний кусок, СЖПРОБЕЛЫ убирает лишнее. В строке 4 запятая прилипла к адресу — это ограничение способа.
В английском Excel: =IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A2,SEARCH(" ",A2&" ",SEARCH("@",A2))-1)," ",REPT(" ",100)),100)),"")
Оговорюсь сразу: формула рабочая, но капризная. Она достаёт только первый адрес, захватывает знаки препинания, которые стоят вплотную к почте, и ломается, если адрес длиннее 100 символов. Прилипшие запятые и скобки придётся вычищать отдельной заменой.
Извлечь e-mail в 2 клика с !SEMTools
Наверняка многим хотелось бы не разбирать пятиэтажную формулу, а просто получить почту из всего столбца. Для этого в !SEMTools есть готовый паттерн:
- Выделите ячейки или столбец с текстом.
- Откройте ИЗВЛЕЧЬ → Символы → По REGEX-паттерну → Готовые паттерны → e-mail-ы.
- Готово: в каждой ячейке остался адрес почты, там, где его не было, ячейка стала пустой.
Результат пишется прямо в исходные ячейки, без формул, протягивания и вставки значений. Если исходный текст нужен, включите на панели флажок «Выводить результат справа»: адреса появятся в новом столбце рядом.
Как ведёт себя готовый паттерн:
- Из ячейки извлекается первый адрес. Если в ячейке их несколько, используйте ИЗВЛЕЧЬ → Символы → По REGEX-паттерну → «Ввести свой, все совпадения»: процедура спросит шаблон и разделитель, например «, ».
- Ищутся адреса с латинским доменом и зоной из 2–4 букв. Почту на .рф и длинных зонах вроде .online лучше доставать своим шаблоном через тот же пункт «Ввести свой» — подойдёт шаблон из раздела про Excel 365.
- Знаки вокруг адреса (скобки, двоеточия, запятые) в результат не попадают.
В бесплатной версии процедура работает с ограничением: в части ячеек вместо результата будет надпись «Доступно в полной версии !SEMTools».
Домен из адреса почты и проверка e-mail
После того как почта извлечена, с ней обычно делают ещё одно из трёх действий:
| Задача | Формула |
|---|---|
| Домен почты (всё после «@») | =ТЕКСТПОСЛЕ(B2;"@") |
| Логин (всё до «@») | =ТЕКСТДО(B2;"@") |
| Похоже ли значение на e-mail | =РЕГТЕСТ(B2;"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$") |
| Сколько ячеек содержат «@» | =СЧЁТЕСЛИ(A2:A1000;"*@*") |
| Домен почты в Excel 2019 | =ПСТР(B2;ПОИСК("@";B2)+1;100) |
Разбор двух первых формул — в статьях о том, как извлечь текст после символа и как извлечь текст до символа. А чтобы собрать из адресов список компаний, удобно сразу извлечь домены: процедура доменов понимает и почту, и ссылки.
Почему почта не извлекается — частые ошибки
- Формула вернула #ИМЯ? — в вашей версии Excel нет РЕГИЗВЛЕЧЬ (она есть только в Microsoft 365). Используйте формулу для Excel 2019 или процедуру.
- Пустой результат при видимом адресе — в адресе кириллица (домен .рф или русская «а» вместо латинской) или собака набрана не тем символом, например «＠». Проверить смешение алфавитов поможет статья про замену кириллицы на латиницу.
- К адресу прилипла запятая или скобка — так работает формула для старых версий. Регулярное выражение такой проблемы не имеет.
- Нашёлся только один адрес из нескольких — РЕГИЗВЛЕЧЬ по умолчанию возвращает первое совпадение, нужен третий аргумент 1.
- Ячейка — гиперссылка «Написать нам», а адреса в тексте нет. Он спрятан в самой ссылке: как его достать, рассказано в статье о том, как вытащить адреса из гиперссылок.
Извлечь e-mail в Excel — частые вопросы
Как извлечь e-mail из ячейки в Excel?
В Excel 365 — формулой =РЕГИЗВЛЕЧЬ(A2;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"). Без формул — процедурой !SEMTools «Готовые паттерны → e-mail-ы».
Как вытащить все адреса почты из одной ячейки?
Формулой =ОБЪЕДИНИТЬ(", ";ИСТИНА;РЕГИЗВЛЕЧЬ(A2;"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}";1)): третий аргумент 1 возвращает все совпадения.
Как извлечь почту в Excel 2016 или 2019?
Формулой =ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(ЛЕВСИМВ(A2;ПОИСК(" ";A2&" ";ПОИСК("@";A2))-1);" ";ПОВТОР(" ";100));100));""). Она берёт первый адрес и может захватить знак препинания рядом с ним.
Как получить домен из адреса почты?
Формулой =ТЕКСТПОСЛЕ(B2;"@") в Excel 365 или =ПСТР(B2;ПОИСК("@";B2)+1;100) в любой версии.
Почему не находится почта на домене .рф?
В стандартном шаблоне домен описан латиницей. Добавьте в часть после «@» диапазон А-Яа-яЁё.
Вытащить адреса почты из тысяч строк с комментариями, контактами и подписями можно без формул: выделили столбец, выбрали готовый паттерн «e-mail-ы» — и в ячейках остались только адреса.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как извлечь e-mail из текста в Excel
Надеюсь, на этой странице вы нашли то, что искали, и смогли достать адреса почты из текста. Если у вас Microsoft 365 и данные должны пересчитываться, берите РЕГИЗВЛЕЧЬ: она точнее и умеет вернуть все адреса сразу. Формула для старых версий подходит для аккуратных данных без знаков вокруг почты. А для разовой и регулярной чистки больших выгрузок быстрее процедура: не нужно ни вспоминать шаблон, ни протягивать формулу, ни потом заменять её значениями.
Вас наверняка заинтересуют и похожие операции: извлечь числа из текста и другие готовые шаблоны из статьи про регулярные выражения в Excel.