Letzte Woche habe ich hier gezeigt, wie man eine einfache Pflichtfeldprüfung in Excel realisiert, so wie sie häufig in Formularen benutzt wird.

Auch diese Woche bleiben wir bei einem ähnlichen Thema:
Wie kann man mit Excel prüfen, ob in ein Formular/eine Zelle eine formal gültige E-Mail-Adresse eingegeben wurde?

Was auf den ersten Blick recht trivial aussieht, erweist sich bei näherer Betrachtung also etwas komplexer. Sei also hiermit gewarnt, wenn es gleich richtig zur Sache geht.

Und so geht’s:

Die E-Mail-Adresse

Wie eine E-Mail-Adresse grundsätzlich aussieht, davon hat jeder eine mehr oder weniger klare Vorstellung. Wie sie aber tatsächlich aussehen darf, darüber macht man sich eher selten Gedanken. Für unsere Gültigkeitsprüfung ist das aber sehr wohl wichtig.

Der technische Aufbau einer E-Mail-Adresse ist z.B. in Wikipedia nachzulesen. Wer es noch ein wenig technischer möchte, dem sei das RFC 5321 zum Simple Mail Transfer Protocol als Lektüre empfohlen.

Vereinfacht gesprochen besteht die E-Mail-Adresse aus dem sogenannten Lokalteil (also der Teil vor dem @-Zeichen) und einem Domainteil (der Teil nach dem @-Zeichen). Der Domainteil hat wiederum mindestens drei Elemente: Einen Hostnamen, einen Punkt und eine sogenannte Top-Level-Domain (kurz TLD), wie „de“ oder „com“.

Darüberhinaus gelten bestimmte Einschränkungen, was die Länge und die enthaltenen Zeichen angeht. Wir wollen uns bei der Prüfung auf die wichtigsten Elemente und Eigenschaften einer E-Mail-Adresse beschränken und die folgenden 10 Regeln überprüfen:

1. Minimallänge der gesamten E-Mail-Adresse: 6
2. Maximallänge der gesamten E-Mail-Adresse: 254
3. @-Zeichen enthalten
4. @-Zeichen nicht an erster oder letzter Stelle
5. Kein Punkt an erster oder letzter Stelle
6. Mindestens ein Punkt im Domaintail enthalten (und nicht unmittelbar nach dem @)
7. Minimallänge des Lokalteils: 1
8. Maximallänge des Lokalteils: 64
9. Minimallänge der Top-Level-Domain (TLD): 2
10. Nur gültige Zeichen verwendet

Eine stattliche Anzahl von Regeln, die wir nun Schritt für Schritt überprüfen wollen. Die gute Nachricht gleich vorweg: Die Beispieldatei, welche sämtliche hier vorgestellten Funktionen enthält, kannst du dir einfach hier herunterladen.

Regel 1: Minimallänge der gesamten E-Mail-Adresse

Eine E-Mail-Adresse muss mindestens 6 Zeichen lang sein. Dies ergibt sich aus den jeweils kürzest möglichen Einzelelementen (Hinweis: die TLD muss mindestens 2 Zeichen lang sein). Diese Prüfung ist mit Hilfe der LÄNGE-Funktion schnell vorgenommen. Das Ergebnis ist entweder WAHR, wenn die Minimallänge eingehalten wurde, oder FALSCH, wenn die Adresse zu kurz ist:

Regel 1: Minimallänge gesamt

Regel 2: Maximallänge der gesamten E-Mail-Adresse

Eine E-Mail-Adresse darf insgesamt nicht länger als 254 Zeichen sein. Auch hier nutzen wir wieder die LÄNGE-Funktion:

Regel 2: Maximallänge gesamt

Regel 3: Ist das @-Zeichen enthalten

Bekanntlich enthält jede E-Mail-Adresse den Klammeraffen. Daher benutzen wir die FINDEN-Funktion, um nach dem @-Zeichen zu suchen. Die Funktion gibt die Position an, an der sich der Klammeraffe befindet. Wird keiner gefunden, liefert die Funktion einen #WERT!-Fehler zurück. Da uns die Position nicht interessiert, sondern nur die Tatsache, ob überhaupt ein @ vorhanden ist, schließen wir das Ganze in die ISTZAHL-Funktion ein:

Regel 3: Klammeraffe vorhanden

Regel 4: @-Zeichen nicht an erster oder letzter Stelle

Auch wenn das @-Zeichen vorhanden ist, darf es nicht an erster oder letzter Stelle der E-Mail-Adresse stehen. Dies prüfen wir mit Hilfe der LINKS- und RECHTS-Funktion. Die umgebende UND-Funktion liefert nur dann ein WAHR zurück, wenn beides nicht der Fall ist:

Regel 4: @ nicht am Anfang und am Ende

Regel 5: Kein Punkt an erster oder letzter Stelle

Analog zum @-Zeichen darf eine E-Mail-Adresse auch nicht mit einem Punkt beginnen oder enden. Die Prüffunktionen sind wieder identisch:

Regel 5: Kein Punkt am Anfang oder Ende

Regel 6: Mindestens ein Punkt im Domaintail enthalten

Jetzt wird es ein wenig spannender: Im Domainteil muss (mindestens) ein Punkt vorhanden sein, darf aber nicht unmittelbar nach dem @-Zeichen stehen. Da es auch Domains gibt, die mehr als einen Punkt enthalten, ist die Prüfung ein wenig kniffeliger:

Regel 6: Punkt im Domainteil

Was passiert hier?

Sehen wir uns die Formel von innen nach außen an. Die innere FINDEN-Funktion liefert die Position des @-Zeichens. Dies ist wichtig, da wir ja den Punkt im Domainteil suchen – also HINTER dem @-Zeichen. Allerdings darf der Punkt nicht unmittelbar hinter diesem @-Zeichen liegen, daher addieren wir noch den Wert 2 zu dieser berechneten Position.

Die äußere FINDEN-Funktion sucht nun nach dem Punkt, beginnt bei der Suche aber an der gerade berechneten Position. Bei der Beispieladresse „ich.auch@richtig.co.uk“ beginnt die Suche also an Position 11 (@-Zeichen liegt an Position 9, dazu noch 2 addieren). Wenn dann noch ein Punkt gefunden wird, wird wieder die Position zurückgeliefert. Bei mehreren Punkten wird nur der erste berücksichtigt, was uns aber ausreicht. Im Beispiel „ich.auch@richtig.co.uk“ also die Position 17.

Und zuletzt prüft die ISTZAHL-Funktion wieder, ob überhaupt ein Punkt (und damit ein Positionswert) gefunden wurde oder ob die innere FINDEN-Funktion stattdessen schon einen #WERT!-Fehler zurückgeliefert hat.

Regel 6: Die Formel aufgedröselt

Regel 7: Minimallänge des Lokalteils

Der Lokalteil – also der Teil vor dem @-Zeichen – muss mindestens 1 Zeichen lang sein. Im Umkehrschluß heißt dass, das @-Zeichen muss sich an Position 2 oder größer befinden:

Regel 7: Minimallänge des Lokalteils

Regel 8: Maximallänge des Lokalteils

Laut Regelwerk darf der Lokalteil maximal 64 Zeichen lang sein. Auch hier prüfen wir wieder den Umkehrschluß, d.h. das @-Zeichen darf höchstens an Position 65 stehen:

Regel 8: Maximallänge des Lokalteils

Regel 9: Minimallänge der Top-Level-Domain (TLD)

Die Top-Level-Domain, also der Teil nach dem letzten Punkt, muss mindestens 2 Zeichen lang sein. Hier wird die Prüfung wieder etwas komplizierter, da es auch E-Mail-Adressen mit Subdomains gibt und damit mehrere Punkte im Domainteil enthalten sein können:

Regel 9: Minimallänge der TLD

Zerlegen wir zum besseren Verständnis dieses Formelmonster wieder in seine Bestandteile:

Bestandteile der Regelprüfung

Zunächst berechnen wir die Länge der Original-E-Mail-Adresse. Im Beispiel also 14. Als nächstes entfernen wir alle Punkte mit Hilfe der WECHSELN-Funktion und ermitteln dann erneut die Länge, was in unserem Fall also 12 ergibt. 14 minus 12 = 2, somit wissen wir, dass die Adresse 2 Punkte enthält.

Dies ist wichtig, da uns ja nur der letzte (hier also der zweite) Punkt interessiert.

Jetzt kommt im Bild in Zeile 17 ein kleiner Trick:
Wir wollen nun lediglich den zweiten Punkt durch ein Sonderzeichen ersetzen. Kein Problem für die WECHSELN-Funktion. Hier kann man nämlich nicht nur angeben, was man wodurch ersetzen möchte, sondern auch ab welchem Vorkommen des gesuchten Zeichens wir mit dem Ersetzen beginnen wollen:
=WECHSELN(Text;Alter Text;Neuer Text;ntes Auftreten)

Als Sonderzeichen brauchen wir eines, welches in einer E-Mail-Adresse unzulässig ist und damit normalerweise nicht vorkommt (welche Zeichen zulässig sind, erfährst du in der 10. Regel). Ich habe mir das ASCII-Zeichen 8 ausgesucht, da man dieses schön erkennen kann: Weißer Punkt auf schwarzem Hintergrund.

Das Ergebnis sieht man im Bild oben in Zelle B17.

In der Zelle B18 prüfen wir, an welcher Position nun dieses gerade eingefügte Sonderzeichen vorkommt. In unserem Beispiel also an Position 12.

Danach stellen wir noch eine letzte Rechnung an: Ist die Gesamtlänge minus der Länge bis zum Sonderzeichen größer oder gleich 2. Mit anderen Worten: Ist der Teil hinter dem Sonderzeichen, also die TLD, länger als 2 Zeichen.

Ich weiß, das war eine knackige Prüfung, die ein wenig Hirnschmalz erfordert.

Regel 10: Nur gültige Zeichen verwendet

Die krönende Regel habe ich mir bis zum Schluß aufgehoben: Werden in der E-Mail-Adresse auch nur gültige Zeichen verwendet? Dazu muss man wissen, dass nur die ASCII-Zeichen 32 – 127 zulässig sind. Im Klartext:

A-Za-z0-9.!#$%&’*+-/=?^_`{|}~

Alle anderen Zeichen dürfen in einer E-Mail-Adresse nicht vorkommen.

Regel 10: Nur gültige Zeichen

Hinweis: Die in dieser Formel verwendete geniale SUMMENPRODUKT-Funktion habe ich mir nicht selbst ausgedacht, sondern auf exelformeln.de gefunden, eine Seite, die es so leider nicht mehr gibt.

Also wieder Schritt für Schritt:

SUMMENPRODUKT im Einsatz

In der Zeile 11 lösen wir das erste Zeichen, nämlich den Buchstaben „m“ mit Hilfe der TEIL-Funktion aus der E-Mail-Adresse heraus:
=TEIL(Text;Erstes Zeichen;Anzahl Zeichen)

Die E-Mail-Adresse hat in unserem Beispiel eine Länge von 17 Zeichen. Für den zweiten Parameter „Erstes Zeichen“ verwenden wir anstelle eines festen Wertes die ZEILE-Funktion „=ZEILE(1:17)“. Dies hat später in der SUMMENPRODUKT-Funktion den Vorteil, dass jedes einzelne der 17 Zeichen nacheinander verwendet wird.

In der Zeile 12 prüfen wir dann mit der FINDEN-Funktion, ob der Buchstabe „m“ in der Liste der erlaubten Zeichen enthalten ist. Dies ist hier der Fall, nämlich an Position 39. Dabei spielt dieser Wert für unsere Zwecke überhaupt keine Rolle, wichtig ist nur, dass überhaupt ein Wert gefunden wird.

In Zeile 13 prüft die SUMMENPRODUKT-Funktion dann das Vorhandensein jedes einzelnen Zeichens der E-Mail-Adresse in meiner Liste mit den gültigen Zeichen. Die dabei ermittelten Positionswerte ergeben in der Summe dann den Wert 801 (auch dieser Wert an sich ist nicht von Bedeutung; Hauptsache, es ist wieder überhaupt ein Wert).

In Zeile 14 prüfen wird dann, ob die SUMMENPRODUKT-Funktion überhaupt eine Zahl geliefert hat. Wenn ja, dann geben wir mit der WENN-Funktion den Wert WAHR zurück, ansonsten FALSCH.

Im Bild oben habe ich das Ganze auch nochmal in der Formelauswertung dargestellt, so dass es vielleicht ein wenig leichter nachvollziehbar wird.

Zusammenfassung

Nun haben wir also mit Hilfe von 10 Regeln die formale Gültigkeit einer E-Mail-Adresse überprüft. Erst wenn alle 10 Prüfungen den Wert WAHR ergeben, handelt es sich um eine gültige Adresse:

Zusammenfassung aller Regeln

(Hinweis: In Kürze erscheint hier auf dem Blog ein ausführlicher Gastartikel zum Thema SUMMENPRODUKT).

Herzlichen Glückwunsch, wenn du bis hierher durchgehalten hast! Das war wirklich ein schönes Stück Arbeit für eine vermeintlich so einfache Angelegenheit.

Auch wenn diese Prüfung nicht 100%ig vollständig ist, sollten damit zumindest die wichtigsten Fehleingaben bei einer E-Mail-Adresse abgefangen werden.

Das könnte dich auch interessieren:

DIE Anlaufstelle rund um Excel & Co.

Blitzschnell mit der Tastatur

Neuberechnung clever nutzen

Eingabemöglichkeiten einschränken