Определение страны по телефонному номеру в Excel с помощью формулы
Если перед вами стоит задача определить страну по телефонному номеру в Excel, используя формулы, этот материал для вас. Мы разберем решение, основанное на вложенных функциях ВПР / VLOOKUP и нескольких таблицах подстановки для телефонных префиксов разной длины.
Материал поможет, если вам нужны понятные формулы в Excel, разбор функций на русском и английском и практические приемы для расчетов без ошибок.
Международный код страны или зоны по E.164 состоит из 1–3 цифр. Более длинный префикс объединяет его с частью национального номера и может уточнять территорию по национальному плану нумерации. Общий код, например +1 в NANP, используется несколькими странами и территориями; одного кода для однозначного определения страны недостаточно.

Совет: создайте и проверьте именованные диапазоны через «Формулы → Диспетчер имён». В каждом диапазоне первая колонка содержит числовые префиксы без плюса, вторая — название страны, территории или общей зоны. Заголовки в диапазон поиска не включайте.
Метод ниже требует собственных справочников префиксов. Файл для скачивания здесь не приложен. Обновляйте справочники по данным ITU и администраторов национальных планов. Не обозначайте общий код названием только одной из использующих его стран; номер не определяет гражданство или фактическое местоположение владельца.

Как работает формула
- Формула проверяет пользовательские префиксы по убыванию длины: 6, 4, 3, 2, 1 цифра. Длины 6 и 4 на иллюстрации — расширения международного кода для уточнения территории, а не длины кода страны по E.164. Если справочнику нужны другие длины, добавьте соответствующие проверки.
- Для извлечения части номера используется функция
ПСТР / MID. - Каждый извлеченный фрагмент ищется в соответствующей именованной таблице (
SIX,Four,Three,Two,One) с помощьюВПР / VLOOKUP. - Функция
ЕСНД (IFNA)обрабатывает только ошибку отсутствия совпадения#Н/Д, переходя к проверке кода следующей длины, если текущий не найден. - Если префикс не найден ни в одном справочнике, формула возвращает «Не найдено». Ошибки преобразования в число, неверные имена диапазонов и другие ошибки ЕСНД не скрывает.
После создания всех пяти двухколоночных именованных диапазонов введите формулу в B1 и скопируйте вниз. При отсутствии расширенных префиксов можно использовать только справочники длиной 3, 2 и 1, убрав внешние два уровня. Для русской локализации:
=ЕСНД(ВПР(ПСТР(A1;2;6)+0;SIX;2;0);ЕСНД(ВПР(ПСТР(A1;2;4)+0;Four;2;0);ЕСНД(ВПР(ПСТР(A1;2;3)+0;Three;2;0);ЕСНД(ВПР(ПСТР(A1;2;2)+0;Two;2;0);ЕСНД(ВПР(ПСТР(A1;2;1)+0;One;2;0);"Не найдено")))))Для английской локализации с запятыми между аргументами:
=IFNA(VLOOKUP(MID(A1,2,6)+0,SIX,2,0),IFNA(VLOOKUP(MID(A1,2,4)+0,Four,2,0),IFNA(VLOOKUP(MID(A1,2,3)+0,Three,2,0),IFNA(VLOOKUP(MID(A1,2,2)+0,Two,2,0),IFNA(VLOOKUP(MID(A1,2,1)+0,One,2,0),"Не найдено")))))Ключевые моменты для успешного применения
- Подготовьте именованные диапазоны для кодов каждой длины.
- Храните исходный номер как текст в виде «+» и 6–15 цифр без пробелов, скобок и дефисов. Приведите номера с 00 и национальными префиксами к этому виду отдельно. Формула ниже предполагает, что ввод уже проверен; она не проверяет существование номера.
- Используйте эту методику для очистки и анализа баз данных клиентов или логистических данных.
Этот подход демонстрирует мощь комбинации базовых функций Excel для решения нетривиальных задач анализа данных без использования макросов или сложных скриптов.