Wie findet man das erste, letzte oder ein bestimmtes Vorkommen eines Zeichens in Excel?
Bei vielen Datenverarbeitungsaufgaben stoßen Sie möglicherweise auf Listen mit Textzeichenfolgen, die Sonderzeichen wie den Bindestrich „-“ enthalten – und müssen die Position ihres ersten, letzten oder eines bestimmten (n-ten) Vorkommens ermitteln. So erfordern beispielsweise das Extrahieren von Informationen anhand von Trennzeichen, die Analyse von Teilecodes oder das Aufteilen von Datenkomponenten oft die präzise Bestimmung der Stelle, an der ein bestimmtes Zeichen innerhalb einer Zeichenfolge vorkommt. Excel bietet jedoch keine direkte integrierte Funktion, um die Position des ersten, letzten oder n-ten Vorkommens eines Zeichens in einer Zelle zurückzugeben. Dieser Artikel stellt mehrere praktische Techniken und schrittweise Lösungen vor, mit denen Sie diese Vorkommen zuverlässig identifizieren und so vielfältige Anforderungen bei der Datenanalyse und -vorbereitung effizient meistern können.
Letztes Vorkommen eines Zeichens mithilfe von Formeln finden
Letztes Vorkommen eines Zeichens mithilfe einer benutzerdefinierten Funktion finden
Erstes oder Vorkommen Vorkommen eines Zeichens mithilfe einer Formel finden
Erstes oder Vorkommen Vorkommen eines bestimmten Zeichens mithilfe einer einfachen Funktion finden
Letztes Vorkommen eines Zeichens mithilfe von Formeln finden
Wenn Sie die Position des letzten Vorkommens eines bestimmten Zeichens (z. B. „-“) in einer Textzeichenfolge ermitteln möchten, bietet Excel dafür keine direkte Standardfunktion. Doch mit einer cleveren Kombination vorhandener Formeln lässt sich diese Aufgabe effizient meistern. Im Folgenden stellen wir Ihnen zwei bewährte Formelansätze vor – besonders wertvoll bei der Arbeit mit Produktcodes, Dateipfaden oder anderen Daten, die einem konsistenten Trennzeichenmuster folgen. Beachten Sie jedoch: Bei großen Datensätzen können komplexe Formeln die Berechnungsleistung beeinträchtigen.
1. Geben Sie in eine leere Zelle (z. B. C2) eine der folgenden Formeln ein oder kopieren Sie sie:
=SEARCH("^^",SUBSTITUTE(A2,"-","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))=LOOKUP(2,1/(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)="-"),ROW(INDIRECT("1:"&LEN(A2))))Beide Formeln liefern die Position (als Zahl) des letzten Vorkommens des Zeichens „-“ in der referenzierten Zelle (A2). Die erste Formel verwendet SUCHEN und WECHSELN, um die Position zu ermitteln, indem das letzte „-“ durch ein eindeutiges Symbol ersetzt und danach gesucht wird. Die zweite Formel nutzt VERWEIS in Kombination mit TEIL und ZEILE, um das letzte „-“ zu finden. Wählen Sie je nach Vertrautheit oder Datenvolumen die passende Methode – der VERWEIS-Ansatz lässt sich in modernen, arrayfähigen Excel-Versionen besonders leicht anpassen.
2. Drücken Sie nach der Eingabe der Formel Enter. Um die Formel auf weitere Zeilen anzuwenden, ziehen Sie den Ausfüllknauf nach unten, bis der gesamte gewünschte Bereich abgedeckt ist. Dadurch wird jede Zelle sofort mit der Position des letzten Vorkommens der jeweiligen Textzeichenfolge gefüllt – wie in der folgenden Abbildung dargestellt. Achten Sie beim Kopieren der Formel in einen anderen Bereich stets darauf, dass die referenzierten Zellen korrekt sind.
Hinweis: In diesen Beispielen bezieht sich A2 auf die Zelle mit Ihren Daten, und „-“ ist das gesuchte Zeichen. Sie können „-“ durch jedes beliebige Zeichen ersetzen, nach dem Sie suchen möchten. Falls Ihre Zelle leer ist oder das Zeichen im Text nicht vorkommt, gibt die Formel möglicherweise einen Fehler zurück. Prüfen Sie bei Texteinträgen mit wiederholten Trennzeichen oder Sonderzeichen sorgfältig auf versteckte oder nicht druckbare Zeichen, die Ihr Ergebnis beeinträchtigen könnten.
Letztes Vorkommen eines Zeichens mithilfe einer benutzerdefinierten Funktion finden
Eine weitere flexible Methode ist die Verwendung einer benutzerdefinierten Funktion (UDF) in einem VBA-Modul, die Ihnen mehr Kontrolle und einfache Wiederverwendbarkeit bietet – besonders dann, wenn Sie häufig das letzte Vorkommen eines beliebigen Zeichens für unterschiedliche Analysen ermitteln müssen. Dieser Ansatz ist ideal, wenn Sie eine übersichtliche Formelschnittstelle ()=lastpositionofchar(Zelle; Zeichen)) bevorzugen, die sich wie eine integrierte Excel-Funktion anfühlt. Beachten Sie jedoch: Damit VBA-Lösungen funktionieren, müssen Makros aktiviert sein. Speichern Sie Ihre Arbeitsmappe daher stets als makrofähige Datei (*.xlsm), um die UDF zu erhalten.
1. Öffnen Sie das Arbeitsblatt, in das Sie diese Funktion einfügen möchten.
2. Drücken Sie gleichzeitig die Tasten ALT + F11, um den Editor Microsoft Visual Basic for Applications zu öffnen.
3. Klicken Sie im VBA-Editor auf Einfügen > Modul, um ein neues Modul zu erstellen. Kopieren Sie anschließend den folgenden Code, und fügen Sie ihn in das Modulfenster ein.
VBA-Code: Letztes Vorkommen eines Zeichens finden
Function LastpositionOfChar(strVal As String, strChar As String) As Long
LastpositionOfChar = InStrRev(strVal, strChar)
End Function
Fehlerbehebung: Sollte die Funktion kein Ergebnis liefern, vergewissern Sie sich, dass Makros aktiviert sind und dass Sie sowohl die korrekte Zellreferenz als auch das richtige Zeichen in der Formel eingegeben haben. Möchten Sie das letzte Vorkommen eines anderen Zeichens suchen, passen Sie einfach das entsprechende Argument entsprechend an.
4. Speichern und schließen Sie das Codefenster. Kehren Sie zu Ihrem Arbeitsblatt zurück und geben Sie in eine leere Zelle (z. B. Zelle B2) die folgende Formel ein: =lastpositionofchar(A2,"-"). Ersetzen Sie A2 durch die entsprechende Zelle und „-“ durch Ihr Zielzeichen, falls erforderlich.
5. Ziehen Sie den Ausfüllknauf nach unten, um die Formel bei Bedarf auf weitere Zellen anzuwenden – so erhalten Sie für jede Textzeichenfolge die letzte Position Ihres angegebenen Zeichens, wie unten dargestellt.
Hinweis: Das erste Argument ()A2) ist die Quellzelle mit Ihrer Textzeichenfolge, und das zweite („-“) ist das gesuchte Zeichen. Sie können diese Parameter bei Bedarf anpassen. Falls keine Übereinstimmung gefunden wird, gibt die Funktion möglicherweise 0 zurück. Speichern Sie Ihre Arbeit stets, bevor Sie VBA-Code ausführen oder bearbeiten. Sollte ein Fehler auftreten, prüfen Sie auf falsch platzierte Anführungszeichen oder fehlerhafte Zellreferenzen.
Erstes oder Vorkommen Vorkommen eines Zeichens mithilfe einer Formel finden
Wenn Ihre Aufgabe darin besteht, nicht nur das letzte, sondern das erste, zweite oder ein beliebiges Vorkommen eines Zeichens in einer Textzeichenfolge zu finden, können Sie eine andere Formelstruktur verwenden. Dies ist oft erforderlich, wenn hierarchische Codes analysiert, Teile strukturierter IDs extrahiert oder wiederholte Symbole in Dateneinträgen identifiziert werden müssen.
1. Um die n-te Position eines bestimmten Zeichens zu ermitteln, geben Sie die folgende Formel in eine leere Zelle (z. B. C2) ein oder fügen Sie sie dort ein:
=FIND(CHAR(160),SUBSTITUTE(A2,"-",CHAR(160),2))In dieser Formel ist A2 die zu durchsuchende Zelle, „-“ das gesuchte Zeichen und 2 gibt an, welches Vorkommen des Zeichens gefunden werden soll (z. B. 2 für das zweite Vorkommen). Passen Sie die Vorkommensnummer ganz einfach an Ihre Datenanforderungen an.
2. Drücken Sie Enter. Um die Formel auf weitere Zeilen anzuwenden, ziehen Sie den Ausfüllknauf bis zum gewünschten Bereich nach unten. So ermitteln Sie beispielsweise die Position des zweiten Vorkommens von „–“ in jeder Textzeichenfolge. Wenn die angegebene Wiederholungsnummer größer ist als die Gesamtanzahl des Zeichens in der Zeichenfolge, gibt die Formel einen Fehler zurück. Überprüfen Sie in diesem Fall Ihre Textdaten oder den Vorkommensparameter.
Hinweis: Die Zahl 2 in der Formel gibt an, um welches Vorkommen des gesuchten Zeichens es sich handelt. Passen Sie diesen Wert an, um die Position des ersten, dritten, vierten oder eines anderen Vorkommens des angegebenen Zeichens zu ermitteln. Wird das Zeichen nicht so oft gefunden, wie angegeben, erscheint ein #WERT!-Fehler – diesen können Sie mit WENNFEHLER() abfangen, um übersichtlichere Ergebnisse zu erhalten.
Finden Sie das erste oder Vorkommen Vorkommen eines bestimmten Zeichens mit einer einfachen Funktion
Für Benutzer, die eine direkte und interaktive Lösung bevorzugen oder sich beim Erstellen komplexer Formeln oder VBA-Codes unsicher fühlen, bietet Kutools für Excel ein praktisches Werkzeug namens Die Position des N-ten Vorkommens eines Zeichens in einem Text finden. Mit diesem Hilfsprogramm ermitteln Sie ganz einfach die Position des ersten, zweiten, dritten oder eines beliebigen weiteren Vorkommens eines bestimmten Zeichens in einer Zelle – und erledigen so die Aufgabe mit nur wenigen Klicks. Das spart besonders bei wiederkehrenden Datenbereinigungsprozessen oder der Arbeit mit großen Datensätzen wertvolle Zeit.
Nach der Installation von Kutools für Excelgehen Sie wie folgt vor, um schnell Ergebnisse zu erhalten:
Angenommen, Sie möchten die Position des zweiten Vorkommens des Bindestrichzeichens „-“ in einer Reihe von Textzeichenfolgen ermitteln:
1. Klicken Sie auf die Zelle, in der das Ergebnis angezeigt werden soll.
2. Klicken Sie im Menüband auf Kutools > Formelhelfer > Formelhelfer, wie unten dargestellt:
3. Im Dialogfeld Formelhelfer:
- Wählen Sie Nachschlagen aus der Formeltyp-Dropdown-Liste.
- Wählen Sie Die Position des N-ten Vorkommens eines Zeichens in einem Text finden aus der Wählen Sie eine Formel aus-Liste aus.
- Geben Sie im Argumenteingabe-Bereich die Zelle mit Ihrem Text an, das zu suchende Zeichen ein und legen Sie die Vorkommensnummer fest.
4. Klicken Sie auf OK. Sobald die Formel das Ergebnis zurückgegeben hat, ziehen Sie einfach den Ausfüllknauf nach unten, um sie bei Bedarf auf weitere Zeilen anzuwenden – so vermeiden Sie Formelfehler durch manuelle Eingabe.
Jetzt Kutools für Excel herunterladen und kostenlos testen!
Diese Option eignet sich besonders für nicht-technische Benutzer oder für solche, die den Prozess häufig über verschiedene Arbeitsblätter hinweg wiederholen müssen, da sie manuelle Fehler minimiert und Zeit beim Erstellen komplexer Formeln spart.
Alternative Formel für neuere Excel-Versionen (mit dynamischen Arrays)
In neueren Excel-Versionen (Excel 365, Excel 2021 und höher), die dynamische Arrays unterstützen, können Sie Funktionen wie SEQUENZ und FILTER nutzen, um alle Positionen eines Zeichens gleichzeitig zu ermitteln. So identifizieren Sie Vorkommen mühelos – oder listen sogar alle Fundstellen zur weiteren Verarbeitung in einem separaten Bereich auf.
1. Geben Sie die folgende Formel in eine leere Zelle (z. B. B2) ein, um alle Positionen des Zeichens „-“ in Zelle A2 zu ermitteln:
=FILTER(SEQUENCE(LEN(A2)), MID(A2,SEQUENCE(LEN(A2)),1)="-")Nach dem Drücken von Enter füllt die Formel automatisch die Position jedes Vorkommens von „-“ in benachbarte Zellen aus. Wenn Sie nur das jeweilige Vorkommen zurückgeben möchten, verwenden Sie Folgendes:
=INDEX(FILTER(SEQUENCE(LEN(A2)), MID(A2,SEQUENCE(LEN(A2)),1)="-"),2)Ersetzen Sie 2 durch die gewünschte Vorkommensnummer. Diese Methode ist besonders leistungsstark für Arrays oder Listen variabler Länge, erfordert jedoch eine Excel-Version, die diese Funktionen unterstützt.
Tipp: Wenn ein #BERECHNUNG!-Fehler oder ein ähnlicher Fehler angezeigt wird, prüfen Sie, ob Ihre Excel-Version dynamische Arrays unterstützt, und stellen Sie sicher, dass die referenzierten Zellen Textdaten enthalten.
VBA-Code zum Finden der Vorkommen Position eines Zeichens
Wenn Sie eine Lösung benötigen, um nicht nur die letzte, sondern jede Position eines Zeichens zu ermitteln, kann Ihnen auch VBA weiterhelfen. Das folgende Makro lässt sich an zahlreiche unterschiedliche Analyseanforderungen anpassen und spart bei wiederholtem Einsatz über mehrere Arbeitsmappen hinweg wertvolle Zeit.
1. Gehen Sie zur Registerkarte Entwicklertools > Visual Basic, und wählen Sie dann Einfügen > Modul. Fügen Sie den folgenden Code in das Modulfenster ein:
Function NthPositionOfChar(cell As Range, ch As String, nth As Integer) As Long
Dim s As String
Dim i As Long, count As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
s = cell.Value
count = 0
For i = 1 To Len(s)
If Mid(s, i, 1) = ch Then
count = count + 1
If count = nth Then
NthPositionOfChar = i
Exit Function
End If
End If
Next i
NthPositionOfChar = 0
End Function2. Schließen Sie den Editor. Verwenden Sie die Formel =NthPositionOfChar(A2,"-",2) (passen Sie die Argumente bei Bedarf an), um in Ihrem Arbeitsblatt die Position des zweiten Vorkommens von „-“ in Zelle A2 zu ermitteln. Beachten Sie: Die Funktion gibt 0 zurück, wenn das gesuchte Vorkommen nicht vorhanden ist.
Dieser Code eignet sich ideal, um flexible Trennzeicheninformationen aus Daten zu extrahieren oder sich wiederholende Analyseaufgaben zu vereinfachen. VBA-benutzerdefinierte Funktionen lassen sich zudem an speziellere Anforderungen anpassen – beispielsweise für die Suche nach zeichensensitiven Mustern oder die Verarbeitung umfangreicher Datensätze.
Achten Sie bei der Arbeit mit Textzeichenfolgen und Positionsangaben von Zeichenvorkommen stets auf versteckte oder nicht druckbare Zeichen – wie Leerzeichen, Wagenrückläufe oder spezielle Unicode-Symbole –, da diese die Ergebnisse Ihrer Formeln beeinflussen können. Stellen Sie zudem sicher, dass Ihre Daten konsistente Trennzeichen verwenden, bevor Sie Formeln oder Makros anwenden. Sollten Fehler auftreten, prüfen Sie Ihre Eingabeparameter, Formelbezüge sowie die Kompatibilität Ihres Datentyps (Text/Zahl) mit der verwendeten Funktion.
Die Kombination aus Formeln, VBA und Kutools-Funktionen bietet maximale Flexibilität – sowohl für alltägliche als auch für anspruchsvolle Textverarbeitungsaufgaben in Excel. Wählen Sie die Methode, die am besten zu Ihrer technischen Erfahrung, Ihrer Excel-Version und der Komplexität Ihrer Daten passt, um optimale Genauigkeit und Effizienz zu erzielen.
Weitere verwandte Artikel:
- Alle Wörter außer dem ersten / letzten Wort in Excel extrahieren
- Das Extrahieren aller Wörter aus einer Zelle – mit Ausnahme des ersten oder letzten – hilft Ihnen, unerwünschte Wörter mühelos zu entfernen. Natürlich könnten Sie die gewünschten Wörter manuell kopieren und in eine andere Zelle einfügen. Doch spätestens bei der Bearbeitung mehrerer Zellen, bei denen jeweils das erste oder letzte Wort weggelassen werden soll, wird dieser Ansatz schnell mühsam. Wie extrahieren Sie also alle Wörter außer dem ersten oder letzten in Excel schnell und einfach?
- Zeichen von rechts nach links in einer Zelle extrahieren
- In diesem Artikel wird beschrieben, wie Zeichen von rechts aus einer Zelle extrahiert werden, bis ein Leerzeichen erreicht ist, um das folgende Ergebnis im Excel-Arbeitsblatt zu erhalten. Eine nützliche Formel in diesem Artikel löst diese Aufgabe schnell und einfach.
- Erste, letzte X Zeichen oder Zeichen an bestimmten Positionen entfernen
- In diesem Artikel erfahren Sie, wie Sie Zeichen von rechts aus einer Zelle extrahieren, bis ein Leerzeichen erreicht ist – und so im Handumdrehen das gewünschte Ergebnis in Ihrem Excel-Arbeitsblatt erhalten. Die vorgestellte Formel meistert diese Aufgabe schnell und einfach.
- Position des ersten Kleinbuchstabens ermitteln
- Wenn Sie eine Liste von Textzeichenfolgen mit gemischter Groß- und Kleinschreibung in einem Excel-Arbeitsblatt haben – wie ermitteln Sie schnell die Position des ersten Kleinbuchstabens, ohne diese manuell zu zählen?
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins