Excel

Soll eine selbst erstellte Funktion in allen Tabellen zur Verfügung stehen ist das recht einfach mittels eines AddIns zu verwirklichen.

Ein paar Schritte müssen dafür getan werden

1. Excel-Datei mit Makros erstellen mit der/den Funktion(en)
    Der Dateiname ist frei wählbar. In diesem Bsp. wurde Funktionensammlung.xlsm gewählt
    Die Excel-Datei enthält 2 Funktionen. Es werden durch eine zusätliche Funktion beim Start die beiden Funktionen registriert.
    Dies hat zur Folge dass die Funktionen eine eigene Kategorie bekommen und die einzelnen Felder einen Hinweistext bekommen.
    Folgende Funktionen werden zur Verfügung gestellt
    - RegEx-Funktion aus dem VBA-Bereich in den normalen Excel-Funktionen zur Verfügung stellen.
    - Datum in englischer Schreibweise darstellen.

2. Excel-Datei als .xla in den Ordner C:\Users\[USER]\AppData\Roaming\Microsoft\AddIns speichern

3. Datei > Optionen => Menüband anpassen
Häckchen bei Entwicklertools setzen und mit OK abschließen

4. In Excel auf das Register Entwicklertool wechseln und Add-Ins anklicken. In dem Dialog-Fenster Funktionensammlung aktivieren und mit OK abschließen.

Ab jetzt stehen in jeder Excel Datei die beiden Funktionen zur Verfügung.

Download: Funktionensammlung.xlsm

Der VBA-Code der in ein Modul gespeichert werden muss.

Option Explicit
'Funtion liefert die Treffer zurück
' https://www.vba-tutorial.de/applikation/regexp.htm


' Es muss ein Verweis auf folgende Bibliothek vorhanden sein "Microsoft VBScript Regular Expression 5.5"
' Aufrufen via VBA-Menü -> Extras -> Verweise.., nach unten scrollen und anklicken
' c:\Windows\ysWOW64\vbscript.dll

' Text       -> Der Text der durchsucht werden muss
' Suchmuster -> Ein Regex-Ausdruck als Suchmuster
' (|) ODER Ausdruck
' {x,y} Länge des Suchmusters minimum, maximum
' [0-9] Zahl von 0 bis 9
' [a-zA-Z] Buchstaben a bis z, Groß und Kleinschreibung

' Email RegEx-Pattern als voreingestelltes Suchmuster
Const cDefautlPattern = "([a-z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,6})"

Const cCategory = "AddIns-Funktionen"

Const cMacroName1 = "RegEx"
Const cMacroName2 = "ConvertDate"

Function RegEx(Txt$, Optional ByVal SearchPattern$ = cDefautlPattern, Optional All = False, Optional MatchSeparator$ = "|", Optional IgnoreCase = True, Optional Multiline = False)
  Dim RegularExpression As New RegExp
  Dim TrefferSammlung As MatchCollection, Treffer As Match
  Dim sTemp$
  
  With RegularExpression
    .Global = All
    .Multiline = Multiline
    .Pattern = SearchPattern
    .IgnoreCase = IgnoreCase
    Set TrefferSammlung = .Execute(Txt)
  End With
    
  For Each Treffer In TrefferSammlung
    'Debug.Print Treffer
    sTemp = sTemp & IIf(sTemp = vbNullString, vbNullString, MatchSeparator) & Treffer
  Next
  RegEx = sTemp
  
  Set TrefferSammlung = Nothing
  Set RegularExpression = Nothing
End Function

Function ConvertDate(Datum, Optional shortMonth As Boolean) As String
  Dim arr, arrMonth
  If Datum <> vbNullString Then
    arr = Split(Datum, ".")
    arr(0) = CInt(arr(0))
    Select Case arr(0)
        Case 1, 21, 31
          arr(0) = arr(0) & "st"
        Case 2, 22
          arr(0) = arr(0) & "nd"
        Case 3, 23
          arr(0) = arr(0) & "rd"
        Case Else
          arr(0) = arr(0) & "th"
    End Select
    ' Monat
    If shortMonth Then
      arrMonth = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    Else
      arrMonth = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
    End If
    arr(1) = arrMonth(arr(1) - 1) & ", "
    ' Zurück packen
    ConvertDate = Join(arr, " ")
  Else
    ConvertDate = Datum
  End If
End Function


' Wird beim öffnen dieser Arbeitsmappe geladen
Public Sub RegisterMyFunction()
  Application.MacroOptions _
    Macro:=cMacroName1, _
    Description:="Per Suchmuster Textteile auslesen", _
    Category:=cCategory, _
    ArgumentDescriptions:=Array( _
      "Feld welches durchsucht werden soll", _
      "Suchmuster (default): " & cDefautlPattern, _
      "Alle Treffer anzeigen (default = FALSCH)", _
      "Trennzeichen bei mehreren Treffern (default = |)", _
      "Groß-/Kleinschreibung ignorieren (default = WAHR)", _
      "Text ist mehrzeilig (default = FALSCH)")
            
  Application.MacroOptions _
    Macro:=cMacroName2, _
    Description:="Datum als Text in englisher Schreibweise", _
    Category:=cCategory, _
    ArgumentDescriptions:=Array( _
      "Feld mit dem Datum", _
      "Monat Kurzschreibweise  (default = FALSCH)")
End Sub

Damit die Funktionen korrekt dargestellt werden muss in DieseArbeitsmappe noch die Funktion RegisterMyFunction eingetragen werden damit diese beim Start aufgerufen werden.

Private Sub Workbook_Open()
  RegisterMyFunction
End Sub

Excel bietet zwei Funktion das erste Vorkommen eines Zeichens zu finden. mit der Funktion Finden wird die Groß- und Kleinschreibung unterschieden mit der Funktion Suchen nicht. Wenn jetzt aber das 2., 3. etc. Vorkommen des Zeichens gesucht wird es deutlich schwieriger wenn es Variable gehalten werden soll. Das Prinzip wird im Artikel "<a href="/index.php/tips-und-tricks.html?amp;view=article&id=321:letztes-vorkommen-eines-zeichens-zeichenkette-in-einem-text-in-excel&catid=74:excel_tt">Letztes Vorkommen eines Zeichens im Text</a>" erklärt. Im Feld A1 ist der zu durchsuchende Text. In Feld A2 die zu suchende Zeichenfolge. In A3 steht wie weit in der Trefferliste gesprungen werden soll. Leer entspricht 0. Wird ein Wert größer des maximalen Treffers eingetragen so wird der letzte Treffer genommen.
A1 = "Guten Morgen liebe Sorgen" A2 = "en" A3 = 2
<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">11 =WENNFEHLER(FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";WENN(WENN(A3=0;1;A3)>(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2);(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2);WENN(A3=0;1;A3))));0)
</pre>
</div>

Leider bietet Excel keine Funktion um die letzte Position eines Zeichens/Zeichenkette zu ermitteln. Als Lösung gibt es 2 Möglichkeiten
Dieser Text steht in der Zelle A1 ->"Guten Morgen liebe Sorgen". in A2 steht die gesuchte Zeichenkette z.B. "en"
<hr />
<h3>Mit internen Funktionen:</h3>
Hierfür werden 4 Funktionen benötigt. Es geht darum das letzte Vorkommen des gesuchten Zeichens/Zeichenfolge durch ein neues Zeichen zu Ersetzen. Dieses Ersatzzeichen darf nicht im Text vorkommen! Dann wird nach diesem "neuen" Zeichen gesucht. 
<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">23 =WENNFEHLER(FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)));0)
</pre>
</div>
 Weiter unten gibt es eine <a href="#ff">Erweiterung </a>der Funktion um den Treffer auf das vorletzte, drittletzte etc. Vorkommen des Zeichens/-folge zu setzten
<hr />
Die Funktionsweise wird in mehreren Schritten erklärt. Als Basis zur Erläuterung wird nach einem LEERZEICHEN " " gesucht. Später ab 4. können auch beliebige Zeichen gesucht werden.
1.) Anzahl des gesuchten Zeichens im Text zählen. Hierfür die Original-Textlänge und die Textlänge ohne das gesuchte Zeichen von einander abgezogen
Mit der Funktion Wechseln kann das gesuchte Zeichen aus der Zeichenkette gelöscht werden. die Funktion LÄNGE liefert die Anzahl an Zeichen des Textes. 
<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">GutenMorgenliebeSorgen =WECHSELN(A1;" ";"")
</pre>
</div>
 Wird jetzt die Länge des gekürzten Textes vom Original-Textes abgezogen ergibt das die Anzahl der gesuchten Zeichen

<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre>3 =LÄNGE("Guten Morgen liebe Sorgen")-LÄNGE(WECHSELN("Guten Morgen liebe Sorgen";" ";""))</pre>
<pre style="margin: 0; line-height: 125%;">3 =LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";""))
</pre>
</div>
 
2.) Das letzte Vorkommen des gesuchten Zeichens duch ein neues ersetzen. Hier im Bsp. wird die Pipe | verwendet. Kann aber mit jedem Zeichen das nicht im Text vorkommt erledigt werden. Die Funktion Wechseln bietet als 4. Parameter die Option beim wievielten Vorkommen des Zeichens dieses durch ein neues ersetzt wird.
<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">Guten Morgen liebe|Sorgen =WECHSELN(A1;" ";"|";3) </pre>
<pre>Guten Morgen liebe|Sorgen =WECHSELN(A1;" ";"|";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";"")))</pre>
</div>
  3.) Jetzt ist es ein leichtes nach dem neuen Zeichen "|" mit der Funktion FINDEN zu suchen.
<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">19 =FINDEN("|";"Guten Morgen liebe|Sorgen")</pre>
<pre>19 =FINDEN("|";WECHSELN(A1;" ";"|";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";""))))</pre>
</div>
 
4.) Wird jetzt nach einem Buchstaben gesucht tritt das Problem der Groß- und Kleinschreibung auf. Mit der Funktion KLEIN kann das behoben werden. Zusätzlich wird jetzt noch das Suchzeichen aus der Zelle A2 verwendet. In A2 steht hier im Bsp "M"
<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">20 =FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);""))))
</pre>
</div>
 5.) Wird nach mehreren Zeichen gesucht muss die Differenz noch durch die Anzahl der Suchzeichen geteilt werden. In A2 steht z.B. "or" 
<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">23 =FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)))
</pre>
</div>
 
6.) Fehler abfangen. Wird im Suchenfeld ein Zeichen/Zeichengruppe eingegeben die nicht vorkommt wird #WERT! angezeigt. Dies hat zur Folge dass darauf basierende Berechnungen ebenfalls diesen Fehler ausgeben. Über die Funktion WENNFEHLER kann das recht einfach abgefangen werden. Der 2. Parameter dieser Funktion bietet die Möglichkeit etwas eigenes im Fehlerfall auszugeben. hier im Bsp ist es einen Null 0 (Hier mit Bsp. wird nach "en" gesucht) 
<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">23 =WENNFEHLER(FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)));0)
</pre>
</div>
 
<hr />
Jetzt noch eine kleine Erweiterung und die Trefferposition zu verändern:
A.) Eine Erweiterung um das vorletzte, drittletzte etc. Vorkommen des Zeichens/-folge zu ermitteln. Hierfür wurde eine 3 Zelle für die Berechnung hinzugefügt. Darin steht wie viele Treffer zurück gezählt werden soll von letzten Vorkommen des Zeichens/-folge. A3 z.B. "1" 
<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">11 =WENNFEHLER(FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)-A3));0)
</pre>
</div>
  B.) Final noch prüfen wenn das Zurücksetzen der Treffer zu groß gewählt wurde. Dann erste vorkommen des Zeichens/-folge verwenden.. Die folgende Funktion liefert die Treffer-Anzahl für die Wechseln-Funktion 
<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">11 =WENN((LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)-A3<=0;1;(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)-A3)
</pre>
</div>
<hr />
<h4><a id="ff">Finale Funktion</a>: </h4>
<div style="background: #f8f8f8; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">11 =WENNFEHLER(FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";WENN((LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)-A3<=0;1;(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)-A3)));0)
</pre>
</div>
 <img src="/images/stories/office/excel/findenr2.png" alt="" />
<hr />
<h3>VBA-Lösung:</h3>
Hierzu muss die Excel-Datei mit Macros gespeichert werden (*.xlsm). Sollte aus Sicherheitsgründen dies nicht möglich sein dann im 2. Teil die Lösung mit verschachtelten Funktionen anwenden. 1. In den VBA-Editor wechseln (ALT+F11) und im Menü auf Einfügen Modul klicken. Jetzt sollte ein neues Modul im Projekt-Explorer angezeigt werden. 2. Im Modul-Fenster folgenden Code einfügen.

<div style="background: #ffffff; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;">
<pre style="margin: 0; line-height: 125%;">Option Explicit
'Diese Funktion liefert das letzte Vorkommen einer Zeichenkette.
'ggf. auch Treffer davor
Function FindenR(ByVal SuchZeichen, ByVal TextZelle, Optional Rueckschritte% = 0)
Dim i%, j%
i = InStrRev(TextZelle, SuchZeichen, , vbTextCompare)
If Rueckschritte > 0 And i > 0 Then
Rueckschritte = Rueckschritte - 1
TextZelle = Left(TextZelle, i - 1)
j = FindenR(SuchZeichen, TextZelle, Rueckschritte)
If j > 0 Then i = j
End If
FindenR = i
End Function
</pre>
</div>
 3.) Zurück in Excel auf Funktionen gehen.     Kategorie Benutzerdefiniert auswählen     Funktion FindenR anklicken und Felder füllen <img src="/images/stories/office/excel/findenr.png" alt="findenr" />

Eigene Excel-Funktion (Makros) allgemein zur Verfügung stellen

Erstes, x-tes Vorkommen eines Zeichens/Zeichenkette in einem Text in Excel

Letztes Vorkommen eines Zeichens/Zeichenkette in einem Text in Excel

Hauptmenü

Anmeldeformular