Blue Flower

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"


Mit internen Funtkionen:

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.

23   =WENNFEHLER(FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)));0)


Weiter unten gibt es eine Erweiterung der Funktion um denTreffer auf das vorletzte, drettletze etc. Vorkommen des Zeichens/-folge zu setzten


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.

GutenMorgenliebeSorgen   =WECHSELN(A1;" ";"")


Wird jetzt die Länge des gekürzten Textes vom Original-Textes abgezogen ergibt das die Anzahl der gesuchten Zeichen

3   =LÄNGE("Guten Morgen liebe Sorgen")-LÄNGE(WECHSELN("Guten Morgen liebe Sorgen";" ";""))
3   =LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";""))

 

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.

Guten Morgen liebe|Sorgen   =WECHSELN(A1;" ";"|";3)
Guten Morgen liebe|Sorgen   =WECHSELN(A1;" ";"|";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";"")))

 
3.) Jetzt ist es ein leichtes nach dem neuen Zeichen "|" mit der Funktion FINDEN zu suchen.

19   =FINDEN("|";"Guten Morgen liebe|Sorgen")
19   =FINDEN("|";WECHSELN(A1;" ";"|";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";""))))

 

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"

20   =FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);""))))


5.) Wird nach mehreren Zeichen gesucht muss die Differenz noch durch die Anzahl der Suchzeichen geteilt werden.
In A2 steht z.B. "or"

23   =FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)))

 

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)

23   =WENNFEHLER(FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)));0)


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 wieviele Treffer zurück gezählt werden soll von letzten Vorkommen des Zeichens/-folge. A3 z.B. "1"

11   =WENNFEHLER(FINDEN("|";WECHSELN(KLEIN(A1);KLEIN(A2);"|";(LÄNGE(A1)-LÄNGE(WECHSELN(KLEIN(A1);KLEIN(A2);"")))/LÄNGE(A2)-A3));0)

 
B.) Final noch prüfen wenn das Zurücksetzen der Treffer zu groß gewählt wurde. Dann erste vorkommen des Zeichnes/-folge verwenden.. Die folgede Funktion liefert die Treffer-Anzahl für die Wechseln-Funktion

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)

Finale Funktion:

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)

 


VBA-Lösung:

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 verschachtlelten 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.

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


3.) Zurück in Excel auf Funtionen gehen.
    Kategorie Benutzerdefiniert auswählen
    Funktion FindenR anklicken und Felder füllen

findenr