TEIL

Finde die nte Position eines Zeichens innerhalb einer Zelle

Roman Formeln , , , , , ,

Angelehnt an den Beitrag "Zählen eines bestimmten Zeichens innerhalb einer Zelle" wird hier eine Formel vorgestellt, wie die Position des nten Zeichens innerhalb einer Zelle ermittelt werden kann. So können ab einer bestimmten Position "platzsparend" bestimmte Informationen extrahiert werden, wie dies ansonsten beispielsweise mittels der Funktion "Text in Spalten" erledigt wird. Für die effektive Extraktion von Inhalten aus der jeweiligen Zeile kommt unter anderem die Formel "TEIL" zum Einsatz, die in dem Beitrag "Textfragmente aus einer Zelle ziehen" erläutert ist.

Mit "nte" Position wird eine beliebige Position innerhalb einer Zelle verstanden, wenn ein Zeichen vor allem mehrmals vorkommt. Als Beispiel habe ich hier einen Datensatz: "2018_08_21_RT_A_BA_1". Uns interessiert daraus beispielsweise der vierte Wert ("RT") - welcher das Kürzel des zuständigen Mitarbeiter darstellt. Demzufolge ist zu ermitteln, an welcher Position der dritte Underscore ("_") innerhalb der Zelle steht. Man könnte selbstverständlich die Zeichen selbst abzählen und eine TEIL-Formel anwenden, sofern die Datensätze konstant sind. Erfasst jedoch jemand beispielsweise das Datum nicht mit vorgängiger 0 (also lediglich eine "8" für den Monat August) und wird dies so vom System zugelassen, trifft die hinterlegte Regel nicht mehr zu. Hier ist eine Übersicht von diesen zwei Fällen. Durch die verwendete Formel kann die Position (10 oder 11) des dritten "_" stets ermittelt werden:

ListeUebersicht

Im bereits erwähnten und verlinkten Beitrag "Zählen eines bestimmten Zeichens innerhalb einer Zelle" ist die Formel "WECHSELN" bereits erklärt. Wir verwenden diese auch in diesem Anwendungsfall. Hier kommt zudem die Formel "FINDEN" zum Einsatz, bei welcher nach einem bestimmten Zeichen oder einer Zeichenfolge gesucht wird. Ich habe zum Ersetzen das ANSI-Zeichen "160" (Leerschritt ohne Zeilenumbruch) verwendet, doch können da auch beliebige Sonderzeichen verwendet werden, die sicher nicht in Eurem Datensatz vorkommen. Die vollumfängliche Formel in Spalte "D" lautet:

=FINDEN(ZEICHEN(160);WECHSELN(A2;C2;ZEICHEN(160);LÄNGE(A2)-LÄNGE(WECHSELN(A2;C2;""))-3))

Die gesamte Formel "WECHSELN(...)" zählt, wie häufig das entsprechende Zeichen vorkommt. Wichtig für die effektive Positionsermittlung ist die "-3". Dadurch wird gesteuert, welches "n" im Fokus steht. Hier könnte selbstverständlich ebenfalls eine dynamische Formel hinterlegt werden. Insgesamt enthält der Datensatz 6 Mal das Zeichen "_". Mit "-3" wird demnach die Position von n = 3 ermittelt. Mit dem ausgegebenen Wert könnte nun wie oben angedeutet die Formel "TEIL" angewandt werden, um die zwei Zeichen danach zu extrahieren. Abschliessend ist hier diese Formel ebenfalls angezeigt:

=TEIL(A2;FINDEN(ZEICHEN(160);WECHSELN(A2;C2;ZEICHEN(160);LÄNGE(A2)-LÄNGE(WECHSELN(A2;C2;""))-3))+1;2)

ListeKuerzel

Die vorgestellte Formel kann auch in einem anderem Kontext verwendet werden: So können Texte und Häufigkeiten sowie Positionen und Satzstellungen analysiert werden, um nur einige Anwendungsfälle zu nennen.

Die Formel "SUCHEN" - nach Fragezeichen/Multiplikationszeichen suchen

Roman Formeln , , , , , , , , , ,

Die Formel "=SUCHEN" ermöglicht die Suche nach einem bestimmten Zeichen oder einer Zeichenfolge innerhalb eines anderen Textes/einer anderen Zelle. Kombiniert mit beispielsweise der Formel "=TEIL" können so clever Textfragmente aus einer Zelle gezogen werden. Bei Platzhalter wie dem Fragezeichen und dem Multiplikationszeichen (*) funktioniert dies jedoch nicht auf anhin. Hier ein Beispiel mit der Formel "=SUCHEN("*";A1)":

suchenachplatzhalter1

Da es sich beim "*" wie erwähnt um einen Platzhalter handelt, wird ein beliebiges Zeichen bereits an erster Stelle im Text in Zelle "A1" gefunden - das Resultat der Formel ist demnach "1". Die Formel muss deshalb leicht angepasst werden: Es benötigt vor dem Platzhalter-Zeichen eine Tilde ("~"), um zu definieren, dass nach exakt diesem Zeichen gesucht werden soll. Die korrekte Formel lautet demzufolge:

=SUCHEN("~*";A1)

Das Resultat sieht nun wie folgt aus (das Zeichen steht an 11. Stelle im Text in Zelle A1):

suchenachplatzhalter2

Dieses Vorgehen deckt sich auch mit der herkömmlichen Suchfunktion in den Office-Programmen ("CTRL + F"), was im Beitrag "Suchen & Ersetzen eines Multiplikationszeichens/Platzhalters (*)" genauer aufgezeigt wird.

Mittels Formel den Tabellenblatt-Name ausgeben

Roman Formeln , , , , , , ,

Mit einer Zusammensetzung von diversen Formeln kann der Name des aktuellen Tabellenblatts ausgegeben werden. Die komplette Formel lautet:

=TEIL(ZELLE("dateiname");SUCHEN("]";ZELLE("dateiname"))+1;100)

Das Ergebnis dieser Formel ist bei meiner Datei: "MeinTabellenblatt". [Beachte, dass dies nur bei bereits abgespeicherten Dateien funktioniert.]

Die drei verschiedenen verwendeten Formeln bewirken folgendes; beginnen wir mit "=ZELLE":

In dieser Funktion wird zuerst nach dem "Infotyp" gefragt - wir wollen, dass der Dateiname der aktiven Zelle ausgegeben wird. Das Ergebnis: C:\....\...\[MeineDatei.xlsx]MeinTabellenblatt. Nicht zwingend ist eine "Referenz" nach dem Infotyp als zweites Argument innerhalb der Formel. Man könnte da auf eine andere Zelle verweisen; auch auf ein anderes Tabellenblatt - und könnte so schlussendlich mit leichtem Modifizieren der obigen Formel den Namen eines anderen Tabellenblattes ausgeben lassen.

Die Formel "=SUCHEN" haben wir im Beitrag "Textfragmente aus einer Zelle ziehen" genauer betrachtet: Es wird damit die Position des Suchtextes (in unserem Beispiel die eckige Klammer "]" nach dem Dateinamen) ausgemacht, ab welcher (+1) die "=TEIL"-Funktion einen entsprechenden Text ausgibt. Die 100 zum Schluss der obigen Formel ist lediglich eingebaut, dass bestimmt genügend Zeichen ausgegeben werden (also ein Tabellenname mit bis zu 100 Zeichen). Diese hohe Zahl ist unkritisch, da keine ungewünschten Leerzeichen an den Text angefügt werden.