Извлечение электронной почты из текстовой строки в таблицу Excel / Google

Question

Я собираюсь извлечь адреса электронной почты из текстовых строк в Google Docs/Excel и автоматически отправлять их по электронной почте, где содержимое ячеек в настоящее время неизвестно (пока пользователь не обновит их).

Например, они могут читать:

1. Привет,

   Мой друг example@example.com.

   Спасибо,

   Пример Джо

ИЛИ ЖЕ

2. Вы должны спросить

   example@example.co.uk

В настоящее время я получил эту формулу:

=IFERROR((LEFT(CELL,FIND("@",CELL)-1))&"@"&(regexextract(CELL,"@(.*)"))) 

Работает практически во всех случаях, кроме

а) как в примере 1, где кто-то ставит, или. в конце письма

б) как в примере 2, когда электронное письмо начинается с новой строки, оно приходит, например, как.

просить

example@example.com

Как я могу адаптировать формулу, чтобы исправить это?

Answer 1

Для таблиц Google

В Google Spreadsheets есть эти классные, уже встроенные формулы регулярных выражений.

• REGEXEXTRACT, REGEXMATCH, REGEXREPLACE

Мы используем первый, чтобы извлечь почтовые адреса. Поместите эту формулу в ячейку таблицы Google:

=iferror(Regexextract(A1;"[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}");"")

• A1 - это ячейка, в которой должна находиться строка для проверки (тело письма в вашем случае)
• Полная формула состоит из 2 частей. Внутренняя часть представляет собой формулу регулярного выражения, а внешняя часть предназначена для предотвращения ошибок.
• Regexextract(A1,"\[A-z0-9._%+-\]+@\[A-z0-9.-\]+\.\[A-z\]{2,4}") возвращает адрес электронной почты
• iferror(innerformula,"") запрещает #N/A когда формула регулярного выражения не может ничего вернуть, например, не найден действительный почтовый адрес

Как работает шаблон регулярных выражений?

[А-z0-9._%+-.]+@ [A-z0-9 .-]+[Az] {2,4}

• A-z представляет любой символ между A и z .
  Обратите внимание на прописные и строчные буквы. Таким образом, это без учета регистра
• 0-9 представляет любую цифру
• ._%+- представляют эти знаки сами
• [ ] представляет один символ, который разрешен в скобках
• Помещение знака + за [ ] позволяет бесконечно повторять предыдущий паттерн
• @ не имеет особого значения. Он буквально ищет знак @
• [A-z0-9.-]+ такой же, как указано выше. Но _%+ не допускается за знаком @ этот раз
• \. ищет одну точку. Это должно быть экранировано предшествующим \ потому что . обычно это заполнитель любого символа
• Наконец [A-z]{2,4} ищет 2,3 или 4 символов без учета регистра

Использованные ресурсы

• http://www.cheatography.com/davechild/cheat-sheets/regular-expressions/

Answer 2

Это для Excel.

Рассмотрим следующую пользовательскую функцию (UDF):

Public Function GetEmailAddy(Sin As String) As String
    Dim s As String
    If InStr(1, Sin, "@") = 0 Then
        GetEmailAddy = ""
        Exit Function
    End If

    s = Replace(Sin, Chr(10), " ")
    s = Replace(s, Chr(13), " ")
    s = Application.WorksheetFunction.Trim(s)
    ary = Split(s, " ")

    For Each a In ary
        If InStr(1, a, "@") > 0 Then
            GetEmailAddy = a
            Exit Function
        End If
    Next a
End Function

Пользовательские функции (UDF) очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать UDF из Excel:

=GetEmailAddy(A1)

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а подробности о UDF смотрите в:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Макросы должны быть включены, чтобы это работало!

Вот некоторые примеры: