Mit ein wenig Vorbereitung kann man auch unter Excel mit Regulären Ausdrücken in Formeln arbeiten. z.B. um Eingabe-Formate zu überprüfen, Ersetzungen vorzunehmen oder einfach nur um Zeichenfolgen aufzufinden. Diese Dokumentation zeigt wie man sich sein Excel so einrichten kann damit es uns dabei unterstützt fehlerhafte EMail Adressen in einer Liste leichter ausfindig machen zu können.
An dieser Stelle fügen wir in unser Excel Dokument einen VBA Codeblock ein welcher uns neue “benutzerdefinierte” Excel Funktionen zur Verfügung stellt.
Option Explicit #Const LateBind = True Public Function RegExMatch(FindIn, FindWhat As String, Optional IgnoreCase As Boolean = True) As Boolean #If Not LateBind Then Dim RE As RegExp, allMatches As MatchCollection, aMatch As Match Set RE = New RegExp #Else Dim RE As Object, allMatches As Object, aMatch As Object Set RE = CreateObject("vbscript.regexp") #End If RE.Pattern = FindWhat RE.IgnoreCase = IgnoreCase RE.Global = True RegExMatch = RE.Test(FindIn) End Function Function RegExSubstitute(ReplaceIn, ReplaceWhat As String, ReplaceWith As String) #If Not LateBind Then Dim RE As RegExp Set RE = New RegExp #Else Dim RE As Object Set RE = CreateObject("vbscript.regexp") #End If RE.Pattern = ReplaceWhat RE.Global = True RegExSubstitute = RE.Replace(ReplaceIn, ReplaceWith) End Function Function RegExFind(FindIn, FindWhat As String, _ Optional IgnoreCase As Boolean = False) Dim i As Long #If Not LateBind Then Dim RE As RegExp, allMatches As MatchCollection, aMatch As Match Set RE = New RegExp #Else Dim RE As Object, allMatches As Object, aMatch As Object Set RE = CreateObject("vbscript.regexp") #End If RE.Pattern = FindWhat RE.IgnoreCase = IgnoreCase RE.Global = True Set allMatches = RE.Execute(FindIn) ReDim rslt(0 To allMatches.Count - 1) For i = 0 To allMatches.Count - 1 rslt(i) = allMatches(i).Value Next i RegExFind = rslt End Function
In unserem Beispiel haben wir ja bereits eine Excel Tabelle mit einer Spalte voller eMail Adressen. Nicht jede eMail Adresse wurde korrekt eingegeben und enthält womöglich illegale Zeichen oder hat ein illegales Format. Die uns nun zur Verfügung stehende neue Excel Funktion “RegExMatch(Zelle;RegExString)” wird uns hierbei helfen. Die Funktion vergleicht die “Zelle” mit dem Regulären Ausdruck “RegExString” und gibt bei einem Treffer ein WAHR, andernfalls ein FALSCH zurück.
Zur Überprüfung von gängigen eMail Adressen hat sich folgender Reguläre Ausdruck bewährt:
^([a-z0-9_\.\-]+)\@(([a-z0-9\-]+\.)+)([a-z]+)$
Um also eine eMail Adresse zu überprüfen könnten wir z.b. in einer Nebenspalte folgende Formel hinterlegen:
=RegExMatch(A5;"^([a-z0-9_\.\-]+)\@(([a-z0-9\-]+\.)+)([a-z]+)$")
Entspricht die Adresse in Zelle “A5” einem gültigen Format erhalten wir an der aktuellen Stelle eine “WAHR” Ausgabe. Dies können wir also leicht nutzen um die Spalte der eMail Adressen mit Hilfe von “bedingter Formatierung” einzufärben wann immer eine eMail Adresse korrekt eingegeben wurde oder auf Wunsch auch umgekehrt, die Zelle soll dich verfärben wenn die Adresse nicht korrekt ist.
Dazu gehen Sie wie folgt vor:
Wenn Sie Ihrer Formel zur bedingten Formatierung eine NICHT() Funktion hinzufügen, können Sie die Funktionsweise auch umkehren, so dass ein Farbwechsel nur bei falschen Adressen vorgenommen wird. Die Formel könnte dann wie folgt aussehen:
=NICHT(RegExMatch(A18;"^([a-z0-9_\.\-]+)\@(([a-z0-9\-]+\.)+)([a-z]+)$"))
Stellt man sich die Befingte Formatierung wie folgt ein, so erhält man einen noch bequemeren Effekt. Hat man in die Zelle nichts eingegeben so bleibt die Zelle Weiß. Hat man eine gültige Adresse eingegeben so färbt sie sich Grün. Falsche Eingaben färben die Adresse nun rot. Somit fallen Fehler noch leichter auf.
— Axel Werner 2010-09-20 19:42
— Axel Werner 2010-09-17 21:11