Wyodrębnianie wiadomości e-mail z ciągu tekstowego w arkuszu kalkulacyjnym excel / google

Chcę wyodrębnić adresy e-mail z ciągów tekstowych w Dokumentach Google / Excel i wysłać je pocztą e-mail automatycznie, gdy zawartość komórek jest obecnie nieznana (dopóki nie zostanie zaktualizowana przez użytkownika).

Na przykład mogą przeczytać:

1. Cześć,

   Mój przyjaciel przyklad@przyklad.com.

   Dzięki,

   Przykład Joe

LUB

2. Powinieneś zapytać

   przykład@example.co.uk

Obecnie mam tę formułę:

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

Działa prawie we wszystkich przypadkach, z wyjątkiem

a) jak w przykładzie 1, w którym ktoś stawia a, lub. na końcu e-maila

b) jak w przykładzie 2, gdzie e-mail zaczyna się od nowej linii, przychodzi jako np.

zapytać

przyklad@przyklad.com

Jak mogę dostosować formułę, aby je poprawić?

Dla arkuszy kalkulacyjnych Google

Arkusze kalkulacyjne Google mają te fajne, już wbudowane formuły wyrażeń regularnych

• REGEXEXTRACT , REGEXMATCH , REGEXREPLACE

Pierwszego używamy do wyodrębnienia adresów e-mail. Umieść tę formułę w komórce arkusza kalkulacyjnego Google:

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

• A1 to komórka, w której powinien znajdować się ciąg do sprawdzenia (w twoim przypadku treść wiadomości)
• Kompletna formuła składa się z 2 części. Część wewnętrzna to formuła wyrażeń regularnych, a część zewnętrzna służy do zapobiegania błędom
• Regexextract(A1,"\[A-z0-9._%+-\]+@\[A-z0-9.-\]+\.\[A-z\]{2,4}") zwraca adres mailowy
• iferror(innerformula,"")zapobiega, #N/Agdy formuła wyrażenia regularnego nie była w stanie niczego zwrócić, np. nie znaleziono prawidłowego adresu e-mail

Jak działa wzór wyrażenia regularnego?

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

• A-zreprezentuje dowolny znak między Aa a z.
  Zwróć uwagę na wielkie i małe litery. W ten sposób nie jest rozróżniana wielkość liter
• 0-9 reprezentuje dowolną cyfrę
• ._%+- same reprezentują te znaki
• [ ] reprezentuje pojedynczy znak, który jest dozwolony w nawiasach
• Umieszczenie +znaku z tyłu [ ]pozwala na nieskończone powtarzanie poprzedniego wzoru
• @nie ma specjalnego znaczenia. Dosłownie szuka @znaku
• [A-z0-9.-]+jest taki sam jak powyżej. Ale tym razem _%+nie wolno im za @tym znakiem
• \.szuka jednego punktu. Musi być poprzedzony znakiem poprzedzającym, \ponieważ .zwykle jest symbolem zastępczym dowolnej postaci
• W końcu [A-z]{2,4}wyszukuje 2,3 lub 4 znaki bez rozróżniania wielkości liter

Użyte zasoby

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

To jest dla Excela.

Rozważ następującą funkcję zdefiniowaną przez użytkownika (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

Funkcje zdefiniowane przez użytkownika (UDF) są bardzo łatwe w instalacji i obsłudze:

1. ALT-F11 wyświetla okno VBE
2. ALT-I ALT-M otwiera nowy moduł
3. wklej rzeczy i zamknij okno VBE

Jeśli zapiszesz skoroszyt, UDF zostanie z nim zapisany. Jeśli używasz wersji Excela późniejszej niż 2003, musisz zapisać plik jako .xlsm zamiast .xlsx

Aby usunąć UDF:

1. wywołać okno VBE jak wyżej
2. wyczyść kod
3. zamknij okno VBE

Aby użyć UDF z Excela:

=GetEmailAddy(A1)

Aby dowiedzieć się więcej o makrach, zobacz:

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

i

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

a szczegółowe informacje na temat UDF można znaleźć:

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

Makra muszą być włączone, aby to działało!

Oto kilka przykładów: