Bestimmt habt Ihr auch schon Listen in Excel erstellt… z.B. einen Email Verteiler! Wenn Ihr das schon einmal gemacht habt, dann kommt Euch dieser Screenshot sicher bekannt vor…

Etwas mühsam ist das schon!

Aber jetzt gibt es Abhilfe! Darf ich vorstellen?

TEXTVERKETTEN

Die Funktion VERKETTEN gibt es in Excel schon lange. Es ist eine der Text Formeln die für mich zum Grundwissen eines jeden Excel Users gehört. Seit Excel 2013 Excel 2019 oder Excel 365 ist VERKETTEN jedoch veraltet und wird mit der neuen TEXTVERKETTEN Funktion ersetzt.

Wie immer bei Microsoft wird diese alte Formel natürlich trotzdem immer noch für eine recht lange Zeit funktionieren!

TEXTVERKETTEN ist aber eine wahre Verbesserung! Man nehme mal das oben beschriebene Problem!

Aber zuerst einmal ein Blick auf die…

Syntax

TEXTVERKETTEN(Trennzeichen; Leer_ignorieren; Text1; [Text2]; …)

Trennzeichen – ist das was wir als Trennzeichen zwischen den zu bildenden Textblöcken verwenden wollen. Das kann irgend ein einzelnes Zeichen, Zahl, oder Buchstaben sein oder auch ein Wort oder Text!

Leer_ignorieren – Dieser Wert kann entweder auf WAHR oder FALSCH gesetzt werden. Wie der name der Variable bereits andeutet, bestimmt man hier ob leere Zellen ignoriert, sprich übersprungen, werden sollen.

Text1 – Dies ist die erste Zelle oder Bereich in dem Text steht der Verkettet werden soll

[Text2] – Man kann mehrere anderen Bereiche der Formel hinzufügen. Ein zweiter Bereich, so wie aller weitere Bereiche, sind optional.

Anmerkung:

Microsoft sagt uns, dass die Liste, die wir mit der Formel erstellen können eine Maximale Zeichenfolge von 32767 Zeichen haben kann (was dem Maximallen Wert einer Zelle entspricht! Zellbegrenzung)

Wird dieser Wert überschritten so zeigt Excel uns ein #WERT! Fehler an!

Wie immer versteht man eine Formel besser wenn man ein Beispiel Problem hat…

Wer die Beispiele gleich in einer Excel Mappe anschauen will, kann dies mit der Download Datei tun:

Beispiel 1: eine einfache Liste

Bei unserem Beispiel von oben wird einiges einfacher…

Mit einer Formel in einer einzigen Zelle (A7) bekommen wir das gewünschte Resultat!
In Zelle A8 steht die Formel, welche sich in Zelle A7 befindet!

Beispiel 2: eine Liste mit leeren Zellen

Aber es wird noch besser, denn dank der Option Leer_ignorieren, müssen wir in Zukunft Listen nicht mehr zuerst auf leere Zeilen überprüfen!

Die Formel befindet sich auch in diesem Beispiel in der Zelle A7 (und als Text in A8)!

Beispiel 3: eine List mit verschiedenen Trennzeichen

Textverketten kann auch dann hilfreich sein, wenn wir mehrere Spalten zusammenführen müssen, jedoch nicht alle Spalten das gleiche Trennzeichen haben sollen.

Im folgenden Beispiel füge ich Stammdaten für ein Unternehmen zusammen:

=TEXTVERKETTEN(A10:E10;FALSCH;A2:E8)

In der Zeile 10 stehen die gewünschten Trennzeichen! Das Trennzeichen, welches nach der Spalte A benutzt wird steht in Zelle A10. Das welches nach der Spalte B kommt in Zelle B10, etc.

Interessant und beachtenswert ist, dass man eben jedes gewünschte Trennzeichen benutzen kann… so auch ein Leerschlag nach Spalte D oder ein – nach Spalte C! Das Trennzeichen in der letzten Spalte ist dann sozusagen als Abschluss einer jeweiligen Reihe bez. eines Datensatzes zu betrachten!

Leere Zeilen und leere Zellen habe ich hier speziell hervorgehoben mit einem farblich markierten Hintergrund. Wie man im unten stehenden Resultat sieht, werden die leeren Datensätze mit übernommen, was aber notwendig ist wenn wir Datensätze mit einzelnen leeren Zellen habe. Denn nur so bleibt die Struktur für alle Daten gleich!

Das Resultat der Formel:

LU,Lukas@unternehmen.com,CH-8000 Zürich;MA,Markus@unternehmen.com,CH-8001 Zürich;,,- ;JO,Johannes@unternehmen.com,DE-80331 München;JU,,ES-28001 Madrid;CA,Carmen@unternehmen.com,CH-3001 Bern;ME,Melanie@unternehmen.com,AT-1010 Wien

Warnhinweis

Entgegen dem was man vermuten könnte, ignoriert die Formel ausgeblendete Zeilen und gefilterte Listen!

Ein Beispiel: Ich filtere eine Liste mit Emails nach Kriterien. Danach benutzte ich die TEXTVERKETTEN Formel um die ausgewählten Email (sprich die Email der gefilterten Resultate) zusammen zu ziehen um diese dann anzuschreiben, so bekomme ich nicht nur meine gefilterten Zeilen, sondern alle Zeilen, und somit alle Email in der gefilterten Liste zurück!

Fazit: TEXTVERKETTEN ignoriert Filter!