Texte zerlegen leichtgemacht 17

Eine bisher umständliche Aufgabe wird dank neuer Tabellenfunktionen endlich einfach!
 

Kürzlich hatte ich ein paar der 14 neuen Tabellenfunktionen vorgestellt, die seit August 2022 in Excel aus Microsoft 365 verfügbar sind. Heute gehts mit drei weiteren sehr hilfreichen Textfunktionen aus dieser Serie weiter:

  • TEXTVOR
  • TEXTNACH
  • TEXTTEILEN

Wie sie funktionieren und was man damit anstellen kann, erfährst du in diesem Beitrag.


Falls du den ersten Artikel zu den Funktionen VSTAPELN und HSTAPELN verpasst hast, kannst du ihn hier nachlesen.

TEXTVOR und TEXTNACH

Das Szenario ist sehr einfach: Du hast einen aus mehreren Teilen bestehenden Text in einer einzigen Zelle und möchtest diesen in die Einzelteile in getrennten Zellen zerlegen. Das kann beispielsweise eine Liste mit E-Mail-Adressen sein, die in den Teil vor (= Lokalteil) und nach (= Domänenteil) dem @-Zeichen aufgeteilt werden sollen.

Eine E-Mail-Adresse soll zerlegt werden

Eine E-Mail-Adresse soll zerlegt werden

Wie meistens in Excel gibt es dafür verschiedene Möglichkeiten:

  • Menü „Daten | Text in Spalten“
  • Power Query
  • Formellösungen

Für eine Formellösung sah in der Vergangenheit beispielsweise so aus:
=LINKS(A4;FINDEN("@";A4)-1)
bzw.
=RECHTS(A4;LÄNGE(A4)-FINDEN("@";A4))

Lösung mit LINKS, RECHTS und FINDEN

Lösung mit LINKS, RECHTS und FINDEN

Während man für den Lokalteil noch mit zwei Tabellenfunktionen auskam (LINKS und FINDEN), waren für den hinteren Teil schon drei Funktionen notwendig. Nicht superkompliziert, aber alles andere als intuitiv. Und für jemanden, der mit Formeln ohnehin auf Kriegsfuß steht, durchaus eine Hürde.

Das ist jetzt vorbei – sofern man Microsoft 365 im Einsatz hat. Dann mit den beiden neuen Funktionen TEXTVOR und TEXTNACH lässt sich das ganz einfach und nachvollziehbar erledigen:
=TEXTVOR(A9;"@")
=TEXTNACH(A9;"@")

Lösung mit TEXTVOR und TEXTNACH

Lösung mit TEXTVOR und TEXTNACH


Für diesen einfachen Fall gibt man also nur den Bezug zu der Textzelle an, dann noch Trennzeichen und das war’s!

Fortgeschrittenes Teilen

Leider ist die Praxis nicht immer ganz so einfach gestrickt. Was macht man denn, wenn das Trennzeichen mehrmals vorkommt, es also mehrere Stellen gibt, an denen der Text geteilt werden soll?
Als Beispiel sei hier eine dreiteilige Steuernummer genannt oder die ISBN von Büchern, die aus 5 Teilen besteht:

Beispiele für drei- und vierteilige Texte

Beispiele für drei- und vierteilige Texte

Beginnen wir mit der Steuernummer. Der vordere Teil ist einfach, denn hier greift wie im vorhergehenden Beispiel die einfache Variante von TEXTVOR mit dem entsprechenden Trennzeichen:
=TEXTVOR(A15;"/")

Für den hinteren Teil verwenden wir wieder TEXTNACH, leider sieht das Ergebnis aber noch nicht so aus, wie wir es gerne hätten:
=TEXTNACH(A15;"/")

Unvollständige Lösung mit TEXTNACH

Unvollständige Lösung mit TEXTNACH


In dieser Variante wird der Text bereits nach dem ersten Vorkommen des Trennzeichens zurückgeliefert.

Daher geben wir einen zusätzlichen dritten Parameter an, welcher besagt, nach welchem Vorkommen des Trennzeichens der Text zurückgegeben werden soll:
=TEXTNACH(A15;"/";2)

Korrekte Lösung mit TEXTNACH

Korrekte Lösung mit TEXTNACH

Kommen wir damit zum etwas kniffeligen Mittelteil. Dazu brauchen wir sowohl TEXTVOR als auch TEXTNACH. TEXTNACH alleine liefert den kompletten Text nach dem ersten Trennzeichen:
=TEXTNACH(A15;"/")

Den Mittelteil bestimmen (Schritt 1)

Den Mittelteil bestimmen (Schritt 1)


Da wir aus diesem Ergebnis nur noch den Teil vor dem Trennzeichen benötigen, umschließen wir die Formel mit einer TEXTVOR-Funktion:
=TEXTVOR(TEXTNACH(A15;"/");"/")
Den Mittelteil bestimmen (Schritt 2)

Den Mittelteil bestimmen (Schritt 2)

Und mit diesem Wissen können wir auch die 5-teilige ISBN aufsplitten. Wir müssen bei der inneren TEXTNACH-Funktion das jeweils gewünschte Vorkommen des Trennzeichens anpassen um das richtige Ergebnis zu erhalten.

Lösung für mehrere Bestandteile

Lösung für mehrere Bestandteile

Warum schwer, wenn’s auch einfach geht?

Ich gebe zu, nur mit TEXTVOR und TEXTNACH kann es schon etwas komplizierter werden, wenn man längere Texte aufteilen möchte. Auch wenn es immer noch einfacher geht, also mit den in der Vergangenheit dafür notwendigen Funktionen LINKS, RECHTS, FINDEN usw.

Aber zum Glück gibt es auch eine geradezu unfassbar einfache alternative Funktion: TEXTTEILEN

Für unser ISBN-Beispiel geben wir dazu nur in der ersten Zelle diese Formel ein:
=TEXTTEILEN(A28;"-")
Und damit wird der Text bei jedem Vorkommen des angegebenen Trennzeichens aufgeteilt. Da es sich um eine dynamische Arrayfunktion handelt, werden nach rechts hin automatisch soviele Zellen belegt, wie eben nötig sind.

Maximaler Komfort mit TEXTTEILEN

Maximaler Komfort mit TEXTTEILEN

Wenn nun der Ausgangstext weitere Abschnitte dazubekommen würde, dann würde sich auch der Ausgabebereich dynamisch um weitere Spalten erweitern, ohne dass die Formel auch nur angefasst werden müsste:

TEXTTEILEN erweitert sich dynamisch

TEXTTEILEN erweitert sich dynamisch

Diese neuen Textfunktionen versprechen wirklich eine große Erleichterung in vielen Anwendungsfällen – sofern man Microsoft 365 nutzen kann. Falls das bei dir noch nicht der Fall ist, kannst du die Funktionen trotzdem mit dem kostenlosen Excel für das Web testen.

Wie findest du diese neuen Funktionen? Lass es uns in den Kommentaren wissen!
 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

17 Gedanken zu “Texte zerlegen leichtgemacht

  • Rolf

    Genial! Wie immer!
    Und auch wie immer: hilfreich!
    Und das Wichtigste: die Erläuterungen sind absolut verständlich. Sogar für mich! 🙂
    Danke dafür!

  • Simon

    Die neuen Funktionen sind genial. Vor allem, wenn man Steings von rechts kommend auftrennen möchte.
    Endlich hat das „=Rechts(Länge()-Finden()…)“ ein ENDE!

    Warum nur kommen diese Funktionen erst jetzt?
    30Jahre nach den Anfängen von Excel?

  • Richard Engl

    Servus Martin,

    gibt es auch eine Kombination, also zuerst Textteilen mit „@“ und dann falls der Name mit „.“ getrennt ist nochmals teilen, das alles in einer Formel?
    BSP:
    zuerst: (TEXTTEILEN(A2;“@“)) Zwischenergebnis dann GROSS2(TEXTTEILEN(A4;“.“))

    rudi.schuricke@aon.at rudi.schuricke aon.at Rudi Schuricke

    LG aus Oberösterreich

    Richard

    • Martin Weiß Autor des Beitrags

      Servus Richard,

      ja, das geht. Die Funktionen müssen nur verschachtelt werden, du hast die Lösung praktisch schon gefunden:

      =GROSS2(TEXTTEILEN(TEXTTEILEN(A2;“@“);“.“))

      Schöne Grüße,
      Martin

      • Richard Engl

        Danke Martin,

        ich habe es probiert und die Zeichen getauscht, Zuerst den Punkt dann @, jetzt passt es:
        =GROSS2(TEXTTEILEN(TEXTTEILEN(A2;“.“);“@“))
        Aus rudi.schuricke@aon.at
        wird nun: Rudi Schuricke At
        die dritte Spalte macht es dazu, stört aber nicht weil ich dann sowieso nochmal kopieren und mit „als Werte einfügen“ abschließen muss.
        Spitzen Tip von Dir, bitte mach weiter so

        Lg
        Richard

        • Martin Weiß Autor des Beitrags

          Hallo Richard,

          stimmt, in meiner Formel war noch der Wurm drin. Nimm‘ stattdessen die hier:

          =GROSS2(TEXTTEILEN(TEXTVOR(A2;“@“);“.“))

          Schöne Grüße,
          Martin

    • Martin Weiß Autor des Beitrags

      Hallo Tobias,

      ja, diese neuen Funktionen sind nur in M365 verfügbar. Neben Makros bieten sich halt noch die bekannten Funktionen (LINKS, RECHTS, TEIL usw.) an, die grundsätzlich auch funktionieren, aber natürlich deutlich unhandlicher sind.

      Schöne Grüße,
      Martin

  • Uwe

    Hallo Martin,
    in den beiden zweiten gelb hinterlegten „Kästen“ müsste m. E. „A9“ statt „A8“ stehen.
    Davon abgesehen wieder ein interessanter Artikel zu neuen Funktionen. Ich bin jedes Mal aufs Neue gespannt, was Du Deinen Leser*innen wieder Interessantes aus der Excel-Welt präsentierst.
    Vielen Dank dafür!

  • Tom

    Hallo Martin,
    bis dato konnte ich deine supertollen Beispiele immer nachvollziehen.
    Seit dem du mit Office 365 experimentierst ist mir dies leider nicht mehr möglich.
    Ich werde auch nicht umsteigen.
    Wenn du bei dem „NEUEN OFFICE“ bleibst, werden sich EINIGE verabschieden. 😪
    Gruß Tom

    • Martin Weiß Autor des Beitrags

      Hallo Tom,

      das wäre sehr schade, wenn du dich deswegen hier verabschieden solltest. Mit der gleichen Argumentation könnten sich aber auch M365-Anwender hier verabschieden, wenn ich die neuen Funktionen ignorieren würde. Oder Anwender von Excel 2013 oder 2010.

      Es wird selbstverständlich auch weiterhin Artikel geben, die in Nicht-M365-Excel-Version nutzbar sind. Aber es wird genauso weiterhin solche geben, die eben nur mit M365 funktionieren. Denn es gehört einfach dazu, dass sich Dinge weiterentwickeln und ich werde versuchen, einigermaßen die Balance zu finden.

      Schöne Grüße,
      Martin