Office-Blog

Microsoft spendiert allen, die in Excel 365 Daten aus fremden Systemen bearbeiten müssen, neue Funktionen. Eine der typischen Aufgaben lässt sich damit einfacher erledigen: Inhalte auf mehrere Spalten aufteilen. Als Alternative zum Befehl Text in Spalten oder zu komplizierten Formellösungen mit LINKS, RECHTS, TEIL, FINDEN etc. hält Excel 365 jetzt neue Funktionen bereit: TEXTNACH, TEXTVOR und TEXTTRENNEN.

Mit 3 neuen Funktionen aus der Kategorie TEXT die Inhalte auf mehrere Spalten aufteilen

Das folgende Beispiel zeigt, welche Erleichterung diese 3 neuen TEXT-Funktionen bringen.

Insgesamt 14 neue Funktionen, aber vorerst nur im Betakanal

Wer hätte das gedacht? Für Excel 365-User fallen Ostern und Weihnachten in diesem Jahr zusammen, und zwar auf den 16. März. An dem Tag nämlich hat Joe McDaid, Programm-Manager des Excel-Teams, 14 neue Funktionen angekündigt. Nachzulesen ist das Ganze hier: Text and array manipulation functions in Excel (office.com)

Besonders interessant sind sicher für viele die drei neuen Funktionen zum Aufteilen von Inhalten einer Spalte auf mehrere Spalten.

Wo und für wen sind die neuen Funktionen verfügbar?

Die neuen Excel-Funktionen sind zunächst nur für diejenigen verfügbar, die als Updatekanal den Betakanal eingestellt haben. Wer seinen Updatekanal und Updatestand prüfen möchte, wählt einfach die Befehlsfolge Datei > Konto.

In dieser Version von Excel 365 sind die neuen Funktionen verfügbar

Praxistest: Inhalt einer Spalte auf drei aufteilen

Bisher gilt: Es gibt vier Optionen, wenn wir Inhalte auf mehrere Spalten aufteilen wollen: Formeln, Text in Spalten, Blitzvorschau oder Power Query. Einige meinen, dass das reiche. Andere haben das Gefühl, dass sei zu kompliziert. Die neuen TEXT-Funktionen sollen hier helfen. Schauen wir es uns dies an dem folgenden Beispiel an. Die Personalnummern in Spalte A enthalten drei Informationen:

• den aus ein bis drei Buchstaben bestehenden Standort der Beschäftigten,
• die dreistellige Ziffer des Fachbereichs und
• die Stammnummer der Beschäftigten, die je nach Standort von zwei bis fünf Ziffern variiert.

Diese drei Angaben werden durch Bindestriche geteilt.

Die Aufgabe: Den Inhalt von Spalte A auf drei Spalten aufteilen mittels Formel

Über Daten > Text in Spalten ließen sich die Angaben aus Spalte A auf die drei benachbarten Spalten verteilen. Allerdings hätte das einen entscheidenden Nachteil: Personalnummern, die nach Zeile 14 neu hinzukommen, müssten erst wieder aufgeteilt werden. Damit ist Text in Spalten ist keine Lösung, die sich dynamisch anpasst. Eine Lösung mit Formeln kann Abhilfe schaffen.

A) Mit TEXTVOR und TEXTNACH den Inhalt auf mehrere Spalten aufteilen

Für den Standort stehen am Anfang von Spalte A ein bis drei Buchstaben. Hier im Beispiel werden sie vom Rest der Personalnummer von einem Bindestrich getrennt. Statt einer komplizierten Formel, die herausfindet, wie viele Stellen vor dem ersten Bindestrich stehen, geht es mit der neuen Funktion TEXTVOR viel einfacher.

Mit der Formel =TEXTVOR(A4;“-„;1) in Zelle B4 trenne ich mühelos die erste Angabe von der Personalnummer ab.

• Als erstes Argument liefere ich TEXTVOR die Information, wo der Text steht. Hier im Beispiel ist es Zelle A4.
• Als zweites Argument gebe ich mit „-“ an, dass das Trennzeichen ein Bindestrich ist.
• Als drittes Argument gibt die 1 an, um das wievielte Vorkommen des Trennzeichens es sich handelt. Mit anderen Worten: die 1 sorgt dafür, dass die Zeichenkette vor dem ersten Bindestrich ausgegeben wird.

TEXTVOR liefert die Angaben vor dem ersten Bindestrich, egal wie viele Zeichen es sind

Die Angabe zur Stammnummer in der dritten Spalte bereitstellen

Für die Stammnummer wird die Zeichenkette gebraucht, die nach dem zweiten Bindestrich steht. Eine perfekte Konstellation für den Einsatz der Funktion TEXTNACH. Daher gebe ich als Formel in Zelle D4 ein: =TEXTNACH(A4;„-„;2). Der Aufbau von TEXTNACH ist identisch zu dem von TEXTVOR. Nur die Suchrichtung ist umgekehrt: das eine Mal wird vor, das andere Mal nach dem Trennzeichen nach einer Zeichenkette gesucht.

Mit TEXTNACH ganz einfach die Zeichenkette nach dem zweiten Trennzeichen ermitteln

Die Angabe zum Fachbereich in der zweiten Spalte anzeigen

Es gibt TEXTVOR und TEXTNACH, aber leider kein TEXTZWISCHEN. Eine solche Funktion wäre ganz praktisch, um die Zeichenkette zwischen den beiden Bindestrichen jeder Personalnummer zu ermitteln. In dem Fall hilft eine Kombination von TEXTNACH und LINKS.

In Zelle C4 gebe ich folgende Formel ein: =LINKS(TEXTNACH(A4;“-„;1);3). Das leistet die Formel:

• Zuerst ermittelt sie mit TEXTNACH(A4;“-„;1) die Zeichenkette, die nach dem ersten Bindestrich folgt.
• Dann werden mit LINKS und der 3 die ersten drei Zeichen ausgelesen, die TEXTNACH soeben geliefert hat.

Auch hier zeigt sich, dass zusammengesetzte Formeln am besten von innen nach außen zu verstehen sind.

Eine Kombination von TEXTNACH und LINKS liefert die Zeichenkette für den Fachbereich

B) Mit TEXTTRENNEN in nur einer Formel den Inhalt auf mehrere Spalten aufteilen

Wer jetzt denkt, dass TEXTVOR und TEXTNACH eine tolle Erleichterung bringen, hat recht. Aber es wird noch besser. Denn es geht noch einfacher und noch kürzer. Außerdem wird es jetzt auch noch dynamisch.

Ich kann mit nur einer Formel den Inhalt der Spalte A auf drei Spalten aufteilen. Dazu verwende ich diesmal die dritte neue Textfunktion in Excel 365. Sie heißt TEXTTRENNEN. Die Syntax ist ebenso einfach wie bei TEXTVOR und TEXTNACH.

Als Formel gebe ich in Zelle B4 ein =TEXTTRENNEN(A4;“-„). Fertig.

• Wie die folgende Abbildung zeigt, ist die Formel nicht nur kürzer, sondern dynamisch. Denn sie erzeugt einen SPILL-Bereich. Das bedeutet: das Ergebnis der Formel wird nicht nur in eine Zelle geschrieben, sondern in mehrere. Hier im Beispiel sind es die Spalten B bis D, die mit nur einer Formel gefüllt werden.
• Die Formel in B4 kopiere ich dann nach unten und fülle so den Bereich B4 bis D14 mit den gesuchten Angaben.

FAZIT: Wer Inhalte auf mehrere Spalten aufteilen will, wird sicher TEXTTRENNEN in den Kreis seiner Favoriten-Funktionen aufnehmen.

Mit nur einer Formel die Inhalte aus Spalte A auf mehrere Spalten aufteilen und zwar dynamisch

Lust auf mehr Input zu den neuen Funktionen in Excel 365?

Wer mehr wissen will zu den SPILL-Funktionen, bei denen mit nur einer Formel in mehreren Zellen Ergebnisse erzeugt werden, wird hier im Blog über folgende URL fündig: https://www.office-kompetenz.de/?s=spill

Wer sich systematisch und in motivierender Lernatmosphäre in die Excel-Neuerungen einarbeiten will, ist bei den Excel-Kompetenztagen vom 27. bis 29. Juni 2022 in Fulda genau richtig. Hier der Flyer zum Herunterladen.

Flyer zu den Excel-Kompetenztagen 2022 per Klick auf das Bild herunterladen