Извлечение контактной информации (Номер телефона, Адрес электронной почты) из текстовых строк в Excel

Работая с контактными данными в Excel, вы нередко сталкиваетесь с тем, что номера телефонов и адреса электронной почты оказываются перемешаны в одной ячейке вместе с именами, физическими адресами и другой информацией. Такое часто происходит при копировании данных из писем, веб-сайтов или импорте из внешних источников.

Если вам нужно извлечь номер телефона и адрес электронной почты в Excel, выполнение этой задачи вручную может занять много времени и привести к ошибкам — особенно при работе с большими объёмами данных или несогласованными форматами. В этом пошаговом руководстве вы узнаете, как быстро извлекать номер телефона и адрес электронной почты из одной ячейки в Excel. Эти методы помогут вам эффективнее очищать, организовывать и структурировать данные.

Извлечение контактной информации (Номер телефона, Адрес электронной почты) в Excel

• Способ 1: с помощью формул (Excel 365)
• Способ 2: с помощью пользовательской функции (все версии Excel)
• Способ 3: с помощью Cells Aide от KUTOOLS AI

Расширенные применения

• Извлечение нескольких контактов в отдельные ячейки
• Стандартизированное форматирование Номер телефона

Заключение

Извлечение контактной информации (Номер телефона, Адрес электронной почты) в Excel

В этом разделе мы представим три практичных способа выполнить эту задачу: с помощью формул в Excel 365, с использованием пользовательской функции, совместимой со всеми версиями Excel, и с помощью KUTOOLS AI для более быстрого и гибкого решения.

Извлечение контактной информации (Номер телефона, Адрес электронной почты) с помощью формул (Excel 365)

Excel 365 поддерживает функции динамических массивов, что значительно упрощает извлечение шаблонов из текста. Если ваша версия Excel включает такие функции, как TEXTJOIN и REGEXEXTRACT, вы можете напрямую извлекать номер телефона и адрес электронной почты с помощью формул.

Извлечение Номер телефона

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

1. Выберите пустую ячейку, в которую нужно поместить извлечённый номер телефона, и скопируйте туда следующую формулу:

   =TEXTJOIN(", ",TRUE,REGEXEXTRACT(A2,"\(?\d{3}\)?[-\s]?\d{3}[-\s]?\d{4}",1))

2. Нажмите клавишу Enter. При необходимости перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам. Если найдено несколько номеров телефона, они будут возвращены в одной ячейке, разделённые запятыми. См. скриншот:
   

Извлечь адреса электронной почты

Если вы используете Excel 365, формулы позволяют быстро и динамично распознавать шаблоны электронной почты и автоматически извлекать их.

1. Щёлкните пустую ячейку, в которую нужно поместить извлечённые адреса электронной почты, и введите формулу:

   =TEXTJOIN(", ",TRUE,REGEXEXTRACT(A2,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}",1))

2. Нажмите клавишу Enter. При необходимости перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам. Если найдено несколько адресов электронной почты, они будут возвращены в одной ячейке, разделённые запятыми. См. скриншот:
   

Извлечение контактной информации (Номер телефона, Адрес электронной почты) с помощью пользовательской функции

Если указанные выше формулы недоступны в вашей версии Excel, пользовательская функция (UDF) станет отличной альтернативой для извлечения контактной информации.

1. Нажмите Alt + F11, чтобы открыть редактор VBA.
2. Затем щёлкните Вставка>Модульи вставьте следующий код:

   Function ExtractContactInfo(txt As String, infoType As String) As String
       Dim reg As Object
       Dim matches As Object
       Dim m As Object
       Dim result As String
       Dim pattern As String
       Set reg = CreateObject("VBScript.RegExp")
       reg.Global = True
       reg.IgnoreCase = True
       Select Case LCase(infoType)
           Case "phone"
               pattern = "\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}"
           Case "email"
               pattern = "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"
           Case Else
               ExtractContactInfo = "Invalid type"
               Exit Function
       End Select
       reg.pattern = pattern
       If reg.Test(txt) Then
           Set matches = reg.Execute(txt)
           For Each m In matches
               result = result & m.Value & ", "
           Next m
           result = Left(result, Len(result) - 2)
       End If
       ExtractContactInfo = result
   End Function

3. Закройте редактор и вернитесь на лист. В пустой ячейке введите следующие формулы:
   • Извлечение Номер телефона:=ExtractContactInfo(A2,"phone")
   • Извлечь адреса электронной почты:=ExtractContactInfo(A2,"email")
4. Затем просто перетащите формулы вниз, чтобы мгновенно извлечь все номера телефонов и адреса электронной почты из списка.
   

Извлечение контактной информации (Номер телефона, Адрес электронной почты) с помощью KUTOOLS AI

Если вы предпочитаете метод, не требующий написания формул или кода VBA, Cells Aide от KUTOOLS AI предлагает гораздо более простой способ извлечения номера телефона и адреса электронной почты из ячеек.

С помощью KUTOOLS AI вы можете извлекать не только номера телефонов и адреса электронной почты, но и имена, а также другие типы информации. Просто опишите в запросе, что именно вы хотите извлечь, — и система вернёт практически любые нужные вам данные из выбранных ячеек.

1. Щёлкните Kutools > KUTOOLS AI > Cells Aide, см. скриншот:
   
2. В диалоговом окне Cells AI All-in-One Aideукажите следующие параметры:
   1. В поле Исходный диапазонвыберите ячейки, содержащие текстовые строки, которые требуется обработать;
   2. Нажмите раскрывающийся список Библиотека подсказок и выберите предопределённый запрос Извлечь контактную информацию.
   3. В поле Запрос содержимое просмотрите или измените запрос в соответствии со своими потребностями. Например:
      Извлеките имя, номер(а) телефона и адрес(а) электронной почты из текста ячейки. Если найдено несколько номеров телефонов или адресов электронной почты, верните их в одной ячейке, разделив запятыми. Если совпадений не найдено, верните пустое значение.
   4. Нажмите кнопку Создать. KUTOOLS AI проанализирует выбранные данные и отобразит извлечённые даты на панели Результат справа.
      
3. После получения результата нажмите кнопку Вставить в диапазон и выберите пустую ячейку для размещения извлечённой информации. Затем нажмите ОК.
   

Результат: все указанные контактные данные — такие как имя, номер телефона и адрес электронной почты — извлекаются в отдельные ячейки для удобного просмотра и использования.

Расширенные применения

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

Извлечение нескольких контактов в отдельные ячейки

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

Следующие формулы могут вам помочь:

Извлечение Номер телефона в отдельные ячейки:

=REGEXEXTRACT(A2,"\(?\d{3}\)?[-\s]?\d{3}[-\s]?\d{4}",1)

Извлечение Извлечь адреса электронной почты в отдельные ячейки:

=REGEXEXTRACT(A2,"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}",1)

Стандартизация формата Номер телефона

Приведённые выше формулы извлекают номер телефона в исходном формате. Чтобы сделать результаты чище и согласованнее, вы можете дополнительно привести их к стандартному виду, например (555) 123-4567, с помощью формул ниже.

=LET(
nums,REGEXEXTRACT(A2,"\(?\d{3}\)?[-\s]?\d{3}[-\s]?\d{4}",1),
TEXTJOIN(", ",TRUE,
MAP(nums,LAMBDA(x,
LET(
d,TEXTJOIN("",TRUE,IFERROR(MID(x,SEQUENCE(LEN(x)),1)*1,"")),
"("&,LEFT(d,3)&,") "&,MID(d,4,3)&,"-"&,RIGHT(d,4)
)
))
)
)

Заключение

В заключение, извлечение номера телефона и адреса электронной почты из текстовых строк в Excel значительно повышает ясность и удобство работы с вашими данными. Независимо от того, выберете ли вы формулы в Excel 365, пользовательскую функцию для совместимости с более широким спектром версий или Cells Aide от KUTOOLS AI для быстрого и гибкого решения, — каждый из этих методов эффективно превращает неструктурированный контактный текст в чётко организованную информацию.

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