Finden Sie die Position des letzten Vorkommens eines Zeichens in Excel

In diesem Tutorial erfahren Sie, wie Sie in Excel die Position des letzten Vorkommens eines Zeichens in einer Zeichenfolge ermitteln.

Vor einigen Tagen ist ein Kollege auf dieses Problem gestoßen.

Er hatte eine Liste von URLs, wie unten gezeigt, und er musste alle Zeichen nach dem letzten Schrägstrich ("/") extrahieren.

Also zum Beispiel von https://example.com/archive/Januar er musste „Januar“ extrahieren.

Es wäre wirklich einfach gewesen, wenn es nur einen Schrägstrich in den URLs gegeben hätte.

Was er hatte, war eine riesige Liste mit Tausenden von URLs unterschiedlicher Länge und einer unterschiedlichen Anzahl von Schrägstrichen.

In solchen Fällen besteht der Trick darin, die Position des letzten Vorkommens des Schrägstrichs in der URL zu finden.

In diesem Tutorial zeige ich Ihnen zwei Möglichkeiten, dies zu tun:

• Verwenden einer Excel-Formel
• Verwenden einer benutzerdefinierten Funktion (erstellt über VBA)

Abrufen der letzten Position eines Zeichens mit Excel-Formel

Wenn Sie die Position des letzten Vorkommens haben, können Sie mit der RIGHT-Funktion einfach alles rechts davon extrahieren.

Hier ist die Formel, die die letzte Position eines Schrägstrichs findet und den gesamten Text rechts davon extrahiert.

=RECHTS(A2,LEN(A2)-FINDEN("@",ERSETZEN(A2,"/","@",LEN(A2)-LEN(ERSETZEN(A2,"/",""))),1 )) 

Wie funktioniert diese Formel?

Lassen Sie uns die Formel aufschlüsseln und erklären, wie jeder Teil davon funktioniert.

• ERSATZ(A2“,/“,“”) - Dieser Teil der Formel ersetzt den Schrägstrich durch eine leere Zeichenfolge. Wenn Sie beispielsweise das Vorkommen einer anderen Zeichenfolge als des Schrägstrichs finden möchten, verwenden Sie dies hier.
• LEN(A2)-LEN(ERSETZEN(A2“,/“,“”)) - Dieser Teil würde Ihnen sagen, wie viele Schrägstriche in der Zeichenfolge vorhanden sind. Es subtrahiert einfach die Länge der Zeichenfolge ohne Schrägstrich von der Länge der Zeichenfolge mit Schrägstrichen.
• ERSATZ(A2“,/““,@“,LEN(A2)-LEN(ERSETZEN(A2“,/““,“))) - Dieser Teil der Formel würde den letzten Schrägstrich durch @ ersetzen. Die Idee ist, diesen Charakter einzigartig zu machen. Sie können jedes beliebige Zeichen verwenden. Stellen Sie nur sicher, dass es eindeutig ist und nicht bereits in der Zeichenfolge vorkommt.
• FINDEN(“@”,ERSETZEN(A2”,/””,@”,LEN(A2)-LEN(ERSETZEN(A2”,/”””))),1) - Dieser Teil der Formel gibt Ihnen die Position des letzten Schrägstrichs.
• LEN(A2)-FIND(“@”,ERSETZEN(A2”,/””,@”,LEN(A2)-LEN(ERSETZEN(A2”,/”””))),1) - Dieser Teil der Formel würde uns sagen, wie viele Zeichen nach dem letzten Schrägstrich vorhanden sind.
• =RECHTS(A2,LEN(A2)-FINDEN(“@”,ERSETZEN(A2”,/””,@”,LEN(A2)-LEN(ERSETZEN(A2”,/”””))),1 )) - Das würde uns jetzt einfach die Zeichenfolge nach dem letzten Schrägstrich geben.

Abrufen der letzten Position eines Zeichens mit der benutzerdefinierten Funktion (VBA)

Während die obige Formel großartig ist und wie ein Zauber funktioniert, ist sie etwas kompliziert.

Wenn Sie mit VBA vertraut sind, können Sie eine benutzerdefinierte Funktion (auch als benutzerdefinierte Funktion bezeichnet) verwenden, die über VBA erstellt wurde. Dies kann die Formel vereinfachen und Zeit sparen, wenn Sie dies häufig tun müssen.

Lassen Sie uns denselben URL-Datensatz verwenden (wie unten gezeigt):

Für diesen Fall habe ich eine Funktion namens LastPosition erstellt, die die letzte Position des angegebenen Zeichens (in diesem Fall ein Schrägstrich) findet.

Hier ist die Formel, die dies tut:

=RECHTS(A2,LEN(A2)-LetztePosition(A2,"/")+1)

Sie können sehen, dass dies viel einfacher ist als das, was wir oben verwendet haben.

So funktioniert das:

• LastPosition - unsere benutzerdefinierte Funktion - gibt die Position des Schrägstrichs zurück. Diese Funktion benötigt zwei Argumente - die Zellreferenz mit der URL und das Zeichen, dessen Position wir finden müssen.
• Die RIGHT-Funktion liefert uns dann alle Zeichen nach dem Schrägstrich.

Hier ist der VBA-Code, der diese Funktion erstellt hat:

Function LastPosition(rCell As Range, rChar As String) 'Diese Funktion liefert die letzte Position des angegebenen Zeichens 'Dieser Code wurde von Sumit Bansal (https://trumpexcel.com) entwickelt Dim rLen As Integer rLen = Len(rCell) For i = rLen To 1 Step -1 If Mid(rCell, i - 1, 1) = rChar Then LastPosition = i Exit Function End If Next i End Function

Damit diese Funktion funktioniert, müssen Sie sie im VB-Editor platzieren. Sobald Sie fertig sind, können Sie diese Funktion wie jede andere normale Excel-Funktion verwenden.

Hier sind die Schritte zum Kopieren und Einfügen dieses Codes in das VB-Back-End:

Hier sind die Schritte, um diesen Code im VB-Editor zu platzieren:

1. Wechseln Sie zur Registerkarte Entwickler.
2. Klicken Sie auf die Visual Basic-Option. Dies öffnet den VB-Editor im Backend.
3. Klicken Sie im Bereich Projekt-Explorer im VB-Editor mit der rechten Maustaste auf ein beliebiges Objekt für die Arbeitsmappe, in die Sie den Code einfügen möchten. Wenn Sie den Projekt-Explorer nicht sehen, gehen Sie zur Registerkarte Ansicht und klicken Sie auf Projekt-Explorer.
4. Gehen Sie zu Einfügen und klicken Sie auf Modul. Dadurch wird ein Modulobjekt für Ihre Arbeitsmappe eingefügt.
5. Kopieren Sie den Code und fügen Sie ihn in das Modulfenster ein.

Jetzt wäre die Formel in allen Arbeitsblättern der Arbeitsmappe verfügbar.

Beachten Sie, dass Sie die Arbeitsmappe im .XLSM-Format speichern müssen, da sie ein Makro enthält. Wenn diese Formel in allen von Ihnen verwendeten Arbeitsmappen verfügbar sein soll, können Sie sie entweder in der persönlichen Makroarbeitsmappe speichern oder daraus ein Add-In erstellen.

Die folgenden Excel-Tutorials könnten Ihnen auch gefallen:

• So erhalten Sie die Wortzahl in Excel.
• So verwenden Sie SVERWEIS mit mehreren Kriterien.
• Suchen Sie das letzte Vorkommen eines Nachschlagewerts in einer Liste in Excel.
• Teilstring in Excel extrahieren.