Извлечь номер телефона из текста в Excel

Процедура доступна в !SEMTools Lite и Pro.

Извлечь номер телефона из текста ячейки в Excel приходится всякий раз, когда контакты записаны как попало: «Иван, +7 (916) 555-12-34», «тел. 84951234567», «Звоните 8 800 555-35-35». В Excel 365 номер достаёт одна формула с регулярным выражением: =РЕГИЗВЛЕЧЬ(A2;"(\+7|8)[\s-]?\(?\d{3}\)?[\s-]?\d{3}[\s-]?\d{2}[\s-]?\d{2}"). В остальных версиях встроенных регулярок нет, а без формул весь столбец обрабатывает готовый паттерн !SEMTools в 2 клика.

Если номер нужно не забрать, а найти строки, где он есть, или удалить такие ячейки, вам в статью про удаление ячеек по содержимому. А адреса почты из того же текста достаются так же просто — смотрите, как извлечь e-mail из текста.

Откуда в ячейках берутся телефоны вперемешку с текстом

Почти всегда это данные, которые вводили люди, а не система. Отсюда и разнобой в записи: скобки, дефисы, пробелы, восьмёрка или +7. Типичные случаи:

• Заявки с сайта и из мессенджеров — «Анна +79265554433, перезвонить после 18».
• Выгрузки CRM, где имя, телефон и комментарий оказались в одном поле.
• Контакты, собранные с сайтов: «Офис: 8 (495) 123-45-67, моб. +7 903 111 22 33». О сборе таких данных — в статье про парсинг сайтов в Excel.
• Подписи и комментарии, где телефон стоит в конце фразы, а формат у каждого свой.

Найти позицию номера функцией ПОИСК тут не выйдет: искать нужно не конкретный символ, а шаблон «+7 или 8, потом 10 цифр с любыми разделителями». Это ровно та задача, для которой придуманы регулярные выражения.

Извлечь телефон формулой в Excel 365

Функция РЕГИЗВЛЕЧЬ возвращает первый фрагмент текста, подходящий под шаблон. Если номера в ячейке нет, она выдаёт #Н/Д, поэтому формулу удобно обернуть в ЕСЛИОШИБКА.

Краткая декомпозиция регулярки:

• (\+7|8) — начало номера: +7 или 8;
• [\s-]? — необязательный пробел или дефис;
• \(?\d{3}\)? — код из трёх цифр, в скобках или без;
• \d{3}, \d{2}, \d{2} — остальные семь цифр группами 3–2–2, между группами снова необязательный пробел или дефис.

Шаблон ловит записи вида «+7 (916) 555-12-34», «8-916-555-12-34», «89161234567», «8 (812) 333-44-55» и «+7 903 111 22 33». Подробнее о синтаксисе и других шаблонах — на странице функции РЕГИЗВЛЕЧЬ.

Все номера из одной ячейки

Когда в ячейке два телефона, например офисный и мобильный, РЕГИЗВЛЕЧЬ с третьим аргументом 1 возвращает все совпадения. Чтобы они не разлились по соседним ячейкам, склейте их функцией ОБЪЕДИНИТЬ:

=ЕСЛИОШИБКА(ОБЪЕДИНИТЬ(", ";ИСТИНА;РЕГИЗВЛЕЧЬ(A2;"(\+7|8)[\s-]?\(?\d{3}\)?[\s-]?\d{3}[\s-]?\d{2}[\s-]?\d{2}";1));"")

Из «Офис: 8 (495) 123-45-67, моб. +7 903 111 22 33» получится «8 (495) 123-45-67, +7 903 111 22 33». Разделитель — первый аргумент ОБЪЕДИНИТЬ, его можно заменить на точку с запятой или перенос строки СИМВОЛ(10).

Привести номера к одному формату

Для рассылок, телефонии и сравнения списков номер обычно нужен в едином виде: +7 и десять цифр подряд. Сначала убираем всё, кроме цифр, затем меняем первую цифру (7 или 8) на +7:

Функция РЕГЗАМЕНИТЬ тоже есть только в Excel 365. В других версиях цифры из номера достаёт извлечение чисел из текста, а единый вид собирается формулой ="+7"&ПРАВСИМВ(C2;10), где в C2 — номер из одних цифр.

Если у вас Excel 2019 или старше

Без регулярных выражений разумной формулы для телефонов нет: номер может стоять где угодно и быть записан десятком способов. Вложенные ПОИСК и ПСТР здесь превращаются в формулу на пол-экрана, которая всё равно ломается на скобках. Есть два выхода.

• Функция REGEXEXTRACT из !SEMTools. Надстройка добавляет в любой Excel, начиная с 2010, функции регулярных выражений с синтаксисом Google Таблиц, и они работают даже в бесплатной версии: =REGEXEXTRACT(A2;"(\+7|8)[\s-]?\(?\d{3}\)?[\s-]?\d{3}[\s-]?\d{2}[\s-]?\d{2}").
• Готовый паттерн !SEMTools, о нём следующий раздел: без формул вообще.

Извлечь номера телефонов в 2 клика с !SEMTools

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

1. Выделите ячейки или столбец с текстом.
2. Откройте ИЗВЛЕЧЬ → Символы → По REGEX-паттерну → Готовые паттерны → Номера телефона (RU).
3. Готово: в каждой ячейке остались только номера, а там, где номера не было, ячейка стала пустой.

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

Как ведёт себя готовый паттерн:

• Ищет российские номера и с +7 или 8 в начале, и без них: «916 555-12-34», «(495) 123-45-67». Шаблон у процедуры шире, чем в формуле выше: между группами цифр допускаются пробел, дефис или точка, а код должен начинаться с 3, 4, 8 или 9, как у российских номеров.
• Достаёт все номера из ячейки, а не только первый. Несколько номеров разделяются вертикальной чертой: «8 (495) 123-45-67|+7 903 111 22 33».
• Номер сохраняется в исходной записи, без приведения к формату. Если +7 или 8 в тексте не было, номер извлекается без них.
• Цифры внутри длинного числа за телефон не принимаются: номер заказа «81234567890123» процедура пропустит.
• Номера других стран (+375, +998, +49) не находятся. Для них используйте ИЗВЛЕЧЬ → Символы → По REGEX-паттерну → «Ввести свой, все совпадения» со своим шаблоном.

В бесплатной версии процедура работает с ограничением: в части ячеек вместо результата будет надпись «Доступно в полной версии !SEMTools».

Почему номер не извлекается — частые ошибки

• Номер записан без +7 и без 8: «916 555-12-34» или «(495) 123-45-67». Шаблон требует +7 или 8 в начале. Если таких записей много, сделайте его необязательным: «(\+7|8)?» в начале. Готовый паттерн !SEMTools такие номера находит сам.
• Разделители не пробел и не дефис: точки «916.555.12.34» или косые черты. Добавьте их в группы: [\s.-]?. В готовом паттерне точки уже учтены.
• Лишние цифры подхватились из длинного числа: номер заказа «81234567890123» шаблон примет за телефон. Проверьте такие строки вручную или ограничьте шаблон границами \b. Готовый паттерн !SEMTools такие числа пропускает.
• Формула вернула #Н/Д — номера в ячейке нет, а ЕСЛИОШИБКА не добавлена.
• Формула вернула #ИМЯ? — у вас Excel без РЕГИЗВЛЕЧЬ (2021 и старше). Используйте REGEXEXTRACT из !SEMTools или готовый паттерн.
• Номер хранится числом, и Excel показывает «7,92E+10». Значение при этом целое, регулярка его найдёт; формат ячейки поменяйте на «Текстовый» или «Числовой» без десятичных знаков.

Извлечь номер телефона в Excel — частые вопросы

Как вытащить номер телефона из текста в Excel?

В Excel 365 формулой =РЕГИЗВЛЕЧЬ(A2;"(\+7|8)[\s-]?\(?\d{3}\)?[\s-]?\d{3}[\s-]?\d{2}[\s-]?\d{2}"). В других версиях — функцией REGEXEXTRACT из !SEMTools или готовым паттерном «Номера телефона (RU)».

Как извлечь все телефоны из одной ячейки?

Третьим аргументом 1 и склейкой: =ОБЪЕДИНИТЬ(", ";ИСТИНА;РЕГИЗВЛЕЧЬ(A2;"(\+7|8)[\s-]?\(?\d{3}\)?[\s-]?\d{3}[\s-]?\d{2}[\s-]?\d{2}";1)).

Как привести номера к виду +7XXXXXXXXXX?

Удалить всё, кроме цифр, и заменить первую цифру: =РЕГЗАМЕНИТЬ(РЕГЗАМЕНИТЬ(A2;"\D";"");"^[78]";"+7").

Почему не находятся номера без восьмёрки?

Шаблон начинается с (\+7|8). Чтобы искать и такие номера, сделайте эту часть необязательной: (\+7|8)?. Готовый паттерн !SEMTools находит такие номера без правок.

Можно ли извлечь номера без формул?

Да: ИЗВЛЕЧЬ → Символы → По REGEX-паттерну → Готовые паттерны → Номера телефона (RU) в !SEMTools обработает весь столбец на месте.

Итоги: как извлечь номер телефона из текста

Шаблон — (\+7|8)[\s-]?\(?\d{3}\)?[\s-]?\d{3}[\s-]?\d{2}[\s-]?\d{2}.

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