Axel Werner's OPEN SOURCE Knowledge Base

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Excel - eMail Adressen mit Hilfe von Regulären Ausdrücken auf Gültigkeit überprüfen ======

{{:it-artikel:windows:excel:regex:screen_015.png }}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. 

===== 1. Vorbereiten der betroffenen Excel Datei =====

An dieser Stelle fügen wir in unser Excel Dokument einen VBA Codeblock ein welcher uns neue "benutzerdefinierte" Excel Funktionen zur Verfügung stellt. 

    - Öffnen Sie Ihre Excel Datei \\ {{:it-artikel:windows:excel:regex:screen_001.png|}}
    - Öffnen Sie im Menü EXTRAS / MAKRO den Punkt "Visual Basic Editor" \\ {{:it-artikel:windows:excel:regex:screen_002.png|}}
    - Suchen Sie nun in der LINKEN BILDSCHIRMHÄLFTE den Namen Ihres Excel Dokumentes \\ {{:it-artikel:windows:excel:regex:screen_004.png|}}
    - Erzeugen Sie an dieser Stelle ein neues "Code Modul" (Modul1) in dem Sie in der LINKEN SPALTE auf dem Namen Ihres Excel Dokumentes die RECHTE Maustaste klicken und aus dem Kontextmenü EINFÜGEN / MODUL auswählen \\ {{:it-artikel:windows:excel:regex:screen_006.png|}}
    - Es öffnet sich ein neues Editor-Fenster mit dem Namen "Modul1". \\ {{:it-artikel:windows:excel:regex:screen_007.png|}}
    - Geben Sie bei dem ROTEN X den nachfolgend gezeigten Codeblock ein: \\ <code>

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

</code>
    - Klicken Sie anschließend auf das SPEICHERN Symbol (Diskette) und schließen Sie den Visual-Basic Editor. Sie befinden sich nun wieder in Ihrem Excel Dokument. Die Vorbereitungen sind damit abgeschlossen und Sie können nun die neuen "Benutzerdefinierten Excel Funktionen" nutzen.


===== 2. Anwenden der neuen Excel-Funktionen: =====

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:

<code>^([a-z0-9_\.\-]+)\@(([a-z0-9\-]+\.)+)([a-z]+)$</code>

Um also eine eMail Adresse zu überprüfen könnten wir z.b. in einer Nebenspalte folgende Formel hinterlegen:

<code>=RegExMatch(A5;"^([a-z0-9_\.\-]+)\@(([a-z0-9\-]+\.)+)([a-z]+)$")</code>

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:

  - Bewegen Sie den Cursor auf die erste eMail-Adress-Zelle Ihrer Liste \\ {{:it-artikel:windows:excel:regex:screen_008.png|}}
  - Wählen Sie aus dem Menü FORMAT den Punkt "BEDINGTE FORMATIERUNG...". Es öffnet sich ein neues Fenster \\ {{:it-artikel:windows:excel:regex:screen_009.png|}}
  - Stellen Sie das Fenster wie gezeigt ein, geben Sie die oben gezeigte Formel ein und klicken Sie anschließend auf den Button "FORMAT" um die Darstellung (Farben) der Zelle zu ändern. 
  - Klicken Sie auf die Registerkarte MUSTER und wählen Sie für die Zelle eine gewünschte Farbe aus, für den Fall das die eingegebene eMail Adresse KORREKT ist. Klicken Sie anschließend auf OK und nochmal OK um beide noch offenen Fenster zu schließen. \\ {{:it-artikel:windows:excel:regex:screen_011.png|}}
  - Sie befinden sich nun wieder in Ihrem Excel Dokument und müssten jetzt sehen dass sich die Farbe der ersten eMail-Adress-Zelle geändert haben müsste, sofern dort ein gültige Adresse hinterlegt wurde. Wenn Sie die eMail Adresse an dieser Stelle nun in eine ungültige ändern, so wird sich die Zelle wieder in Ihre eigentliche "Ursprungsfarbe" verändern. \\ {{:it-artikel:windows:excel:regex:screen_012.png|}}
  - Wenn Sie nun möchten dass die Zellen der Spalte "EMail Adressen" von ROT (illegale Eingabe) nach GRÜN (korrekte Eingabe) umspringen sollen, müssen Sie nur noch ALLE ZELLEN dieser Spalte grundsätzlich ROT einfärben (Hintergrundfarbe). Bei korrekter Eingabe einer EMail Adresse wird sich eine solche Zelle auch dann noch weiterhin GRÜN verfärben. \\ {{:it-artikel:windows:excel:regex:screen_013.png|}}
  - Wie sie sehen stimmt im Augenblick in unserem Beispiel nur die "Bedingte Formatierung" der ERSTEN eMail Zelle. Alle anderen Zellen sind selbst dann noch ROT wenn eine korrekte eMail Adresse eingegeben wurde. Dies liegt daran dass wir bislang nur die erste Zelle mit der "Bedingten Formatierung" behandelt haben. Um dies auch auf die anderen eMail Zellen zu übertragen verwenden Sie das "Pinsel Symbol". Markieren Sie dazu zunächst die erste korrekt funktionierende eMail Zelle, klicken dann auf das Pinsel Symbol und anschließend markieren Sie alle weiteren eMail Zellen. Sodann wird auch die Bedingte Formatierung übernommen. \\ {{:it-artikel:windows:excel:regex:screen_014.png|}}

==== Ein Tip: ====
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:
<code>=NICHT(RegExMatch(A18;"^([a-z0-9_\.\-]+)\@(([a-z0-9\-]+\.)+)([a-z]+)$"))</code>

==== Noch ein Tip: ====

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.  \\ {{:it-artikel:windows:excel:regex:excel-bedingte-format-leet-gut-schlecht.png|}}

 --- //[[mail@awerner.myhome-server.de|Axel Werner]] 2010-09-20 19:42//

===== Quellen und Files: =====

  * http://www.dailydoseofexcel.com/archives/2005/08/13/pattern-matching/
  * http://www.tmehta.com/regexp/
  * Microsoft Demo Datei: {{:it-artikel:windows:excel:regex:regexp-sept-1-2004.xls|}}
  * Meine Demo Datei {{:it-artikel:windows:excel:regex:regex-test-aw.xls|}}
   


 --- //[[mail@awerner.myhome-server.de|Axel Werner]] 2010-09-17 21:11//