Email Adresse von Excel auf Richtigkeit prüfen lassen

[Jabini]

Hallo an alle,

ich bin nicht besonders fit in Ecxel-Formeln und komme bei dieser Problematik nicht weiter:
Ich habe eine lange Liste mit Mitgliedern eines Vereins und hier auch eine Spalte mit deren E-Mail Adressen.
Diese sammeln sich seit vielen Jahren an. Da ich wenig Lust habe jede einzelne E-Mail Adresse der Mitglieder durchzuschauen und auf Richtigkeit zu überprüfen,
würde ich dies gerne mit einer Formel machen. D.h. Excel soll prüfen ob ein @ enthalten ist, eine Domain angegeben ist, ein Punkt enthalten ist, keine Sonderzeichen und Umlaute vorhanden sind und das Ende mit de/com/... endet.
Im Internet finde ich hierzu nichts, was funktioniert. Kann mir jemand dabei helfen das möglich zu machen?

Vielen Dank und liebe Grüße

Janina

[Egon12]

Hallo Janina,

Einigermaßen brauchbar prüfen kann man dies mit Regex.

Gruß Uwe

[J.K.H.]

Hallo Janina,

musst Du halt ggf. noch etwas anpassen/erweitern.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
3		abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ@.
4
5		@	Endung	Keine Sonderzeichen
6	max@mustermann.com	OK	OK	OK
7	MAXMUSTERMANN	kein @ enthalten	verkehrte Domain	OK
8	max(at)mustermann.de	kein @ enthalten	OK	enthält ungültige Zeichen

Zelle	Formel
B6	=WENN(ISTZAHL(WENNFEHLER(SUCHEN("@";A6;1);""));"OK";"kein @ enthalten")
C6	=WENN(ISTZAHL(WENNFEHLER(WENNFEHLER(SUCHEN(".de";A6;1);SUCHEN(".com";A6;1));""));"OK";"verkehrte Domain")
D6	=WENN(ISTZAHL(SUMMENPRODUKT(SUCHEN(TEIL(A6;ZEILE($A$1:$A$999);1);B$3;1)));"OK";"enthält ungültige Zeichen")
B7	=WENN(ISTZAHL(WENNFEHLER(SUCHEN("@";A7;1);""));"OK";"kein @ enthalten")
C7	=WENN(ISTZAHL(WENNFEHLER(WENNFEHLER(SUCHEN(".de";A7;1);SUCHEN(".com";A7;1));""));"OK";"verkehrte Domain")
D7	=WENN(ISTZAHL(SUMMENPRODUKT(SUCHEN(TEIL(A7;ZEILE($A$1:$A$999);1);B$3;1)));"OK";"enthält ungültige Zeichen")
B8	=WENN(ISTZAHL(WENNFEHLER(SUCHEN("@";A8;1);""));"OK";"kein @ enthalten")
C8	=WENN(ISTZAHL(WENNFEHLER(WENNFEHLER(SUCHEN(".de";A8;1);SUCHEN(".com";A8;1));""));"OK";"verkehrte Domain")
D8	=WENN(ISTZAHL(SUMMENPRODUKT(SUCHEN(TEIL(A8;ZEILE($A$1:$A$999);1);B$3;1)));"OK";"enthält ungültige Zeichen")

[RPP63]

Moin!
Ob 2024 die Regex-Funktionen beinhaltet, weiß ich nicht.
Falls ja hier mal eine mögliche Lösung.
Für das Pattern habe ich mich künstlicher Intelligenz bedient.

	A	B
1	Adresse
2	x.yz@abc.com	WAHR
3	a_bgmail.com	FALSCH
4	Ich bins@xyz.de	FALSCH
5	a@b.c	FALSCH
6	Meins@x.org	WAHR

Zelle	Formel
B2	=REGEXTESTEN(A2;"[\w\.-]+@[a-zA-Z\d\.-]+\.[a-zA-Z]{2,}(?!.*\s).+")

Gruß Ralf

[Egon12]

Hallo Janina,
 
falls die Regex Formel in O2024 noch nicht vorhanden ist, hier noch ein Weg via VBA/Regexp wo per Doppelklick auf die Zelle der E-Mail-Adressen u.U. fehlerhafte Adressen einigermaßen brauchbar aufgefunden werden können.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim objRegEx As Object, objMatch As Object
    If Not Intersect(Target, Range("A2:A5")) Is Nothing Then
        Set objRegEx = CreateObject("vbscript.regexp")
        With objRegEx
            .Global = True
            '.Pattern = "[a-z0-9\-\.]{2,63}@[a-z0-9\-\.]{2,63}\.[a-z]{2,4}" ' für den Fall dass im local-part Leerzeichen zugelassen sind.
            .Pattern = "^[a-z0-9\-\.]{2,63}@[a-z0-9\-\.]{2,63}\.[a-z]{2,4}$"
            .IgnoreCase = True
            Set objMatch = .Execute(Target)
        End With
        If objMatch.Count = 0 Then
            Target.Interior.Color = vbRed
        Else
            Target.Interior.Color = xlNone
        End If
        Set objRegEx = Nothing
        Cancel = True
    End If
End Sub

  Email Test via RegEx mit doppelklick.xlsm (Größe: 14,94 KB / Downloads: 3)

Gruß Uwe

[ws-53]

Die REGEXTESTEN würde ich tendenziell in der "Datenüberprüfung" einsetzen wollen, damit nur noch syntaktisch korrekte E-Mailadressen erfasst werden können. 

Um aber zu erfahren, welche überhaupt noch aktiv sind, würde ich eine Testmail an alle schicken, um zu erfahren, welche nicht (mehr) zugestellt werden können.

[derHoepp]

Moin,

auch Powerquery kann hilfreich sein:

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    NurGueltige = Table.SelectRows(Quelle, each isValidEmail([Email]))
in
    NurGueltige

dafür musst du lediglich eine Function schreiben, die deine Kriterien umsetzt. Bei Stackoverflow findest du ein passendes Gerüst: https://stackoverflow.com/a/61176764

Viele Grüße
derHöpp