Die Excel-Funktionen TEXTKETTE und TEXTVERKETTEN sind zwei neuere Varianten der bekannten VERKETTEN-Formel und bieten eine etwas einfachere Anwendung und neue Möglichkeiten. Alle drei Formeln sind dafür da, die Inhalte (Texte und/oder Zahlenfolgen) in mehreren Zellen zu einem durchgehenden Text in einer einzigen neuen Zelle zu verbinden. Wahlweise mit Leerzeichen, Trennzeichen oder Zeilenumbruch zwischen den fusionierten Texteinheiten.
- Die in die Zielzelle eingegebene Verkettungs-Formel lässt sich dann per Mausklick auf ganze Spalten erweitern und damit automatisch auf einen beliebig großen Tabellenbereich anwenden.
- Wie TEXTKETTE, VERKETTEN und TEXTVERKETTEN nun genau konkret funktionieren – und was die Unterschiede sind – erfahren Sie hier in dieser Büro-Kaizen-Anleitung!
Inhaltsverzeichnis
1. Wie funktionieren VERKETTEN, TEXTKETTE & TEXTVERKETTEN in Excel?
Die Funktion VERKETTEN ist seit Microsoft Excel 2003 verfügbar. 2016 kam für Microsoft Office 365 das neue TEXTVERKETTEN hinzu, das die VERKETTEN-Funktion künftig vollständig ersetzen soll. Und seit Excel 2019 ist auch die Variante TEXTVERKETTEN verfügbar. Alle drei Funktionen können die Daten verschiedener Zellen in einer Zelle verbinden und zusammenführen.
- Ein klassisches Anwendungsbeispiel ist, wenn in einer Tabelle die Vornamen, Nachnamen und das Alter einer Person in jeweils eigenen Spalten stehen.
- Dann kann man diese einzelnen Inhalte mit der Verkettungsformel in einer einzigen Zelle fusionieren.
- Das Ausgabeergebnis in der Zielzelle wäre dann zum Beispiel → „Max Mustermann, 32“.
- Bei Bedarf können die so zusammengeführten Daten auch noch um zusätzliche benutzerdefinierte Textinhalte ergänzt werden. So kann in unserem Beispiel der zusätzliche Textzusatz „Jahre alt“ hinzugefügt werden, so dass die Ausgabe dann lautet → „Max Mustermann, 32 Jahre alt“.
Hier die konkreten Eigenschaften & Unterschiede zwischen den drei Verkettungsformeln in Excel:
VERKETTEN in Excel:(englisch: CONCATENATE) |
TEXTKETTE in Excel:(englisch: CONCAT) |
TEXTVERKETTEN in Excel:(englisch: TEXTJOIN) |
| Syntax:
=VERKETTEN(Text1;[Text2];..) |
Syntax:
=TEXTKETTE(Text1;[Text2];..) |
Syntax:
=TEXTVERKETTEN(Trennzeichen; Leer_ignorieren;Text1; …) |
| Vereinfachte Schreibweise:
=VERKETTEN(Zelle1;Zelle2;…) |
Vereinfachte Schreibweise:
=TEXTKETTE(Zelle1;Zelle2;…) |
Vereinfachte Schreibweise:
=TEXTVERKETTEN(Trennzeichen; Leere-Zellen-ignorieren_ja-oder-nein; Zelle1;Zelle2; …) |
|
Diese „Grundformel“ führt die Texte und Ziffern in verschiedenen Zellen in einer neuen Zelle ohne Trennzeichen oder Abstand zusammen. Es können allerdings keine zusammenhängenden Zellbereiche angegeben werden. Trennzeichen (wie zum Beispiel Komma oder Leerzeichen) können optional beliebig manuell zwischen jedem einzelnen Argument eingefügt werden (siehe unten). |
Der Hauptunterschied zur Verketten-Formel ist, dass mit der TEXTKETTE nun auch ganze Zellbereiche als Argument angegeben werden können. Zum Beispiel der Tabellenbereich A4 bis C9 als → „A4:C9“. Auch der Formelname ist selbsterklärender, dass es sich hierbei um das Zusammenführen von Texten und Ziffern zu einer zusammenhängenden Zeichenfolge handelt und nicht um etwa eine Addition. |
Im Unterschied zur TEXTKETTE kann man in dieser Formel von Anfang an ein Trennzeichen eingeben (z. B. Komma und/oder Leerzeichen etc.), das dann automatisch zwischen jedes einzelne Argument eingefügt wird. Das TEXTVERKETTEN bietet zudem eine IgnoreEmpty-Option, um leere Zellen für das Ergebnis wahlweise zu ignorieren oder eben nicht. |
→ Tipp: Sie können sich das Eingeben der Semikolons (Strichkommas) zur Abtrennung der einzelnen Argumente in allen drei Excel-Formeln komplett sparen.
→ Tippen Sie dafür lediglich den Anfang der Formel in eine Zelle und klicken Sie dann für die Zellbezüge „Text1;Text2;Text3“ etc. einfach die einzelnen Zellen mit gedrückter Strg-Taste in der gewünschten Reihenfolge zusammen.
→ Excel fügt dann automatisch immer das für die Abgrenzung der einzelnen Formel-Argumente benötigte Semikolon zwischen die Zellbezüge ein.
2. Excel-VERKETTEN mit zusätzlichem Satzzeichen (Leerzeichen, Komma, Umbruch etc.)
|
Der große Vorteil der VERKETTEN und TEXTKETTE-Formel in Excel ist, dass Sie zwischen jedem der zusammenzuführenden Elemente ganz flexibel unterschiedliche Trennzeichen, Satzzeichen oder auch zusätzliche Texteingaben Ihrer Wahl einfügen können. Der Nachteil ist, dass Sie dies für alle ausgewählten Zellbereiche manuell machen müssen, denn ansonsten werden die Zellinhalte ohne jeglichen Abstand zueinander fusioniert. |
Der große Vorteil der TEXTVERKETTEN-Formel in Excel ist, dass Sie für die Verkettung der Texte ein Trennzeichen vorgeben können, das dann automatisch zwischen den einzelnen Zellinhalten eingefügt wird. Der Nachteil ist, dass Sie dieses Trennzeichen zwischen den verschiedenen Zellbereichen nicht variieren können (z. B. an einer Stelle ein Leerzeichen und an einer anderen ein Umbruch, um z. B. eine Postanschrift zu erzeugen). |
Anleitung: VERKETTEN und TEXTKETTE in Excel mit Trennzeichen und Zeilenumbruch
Sie können in die Verkettung der Zellinhalte beliebige Trennzeichen einfügen (zum Beispiel Bindestrich, Komma, Semikolon, Leerzeichen, Doppelpunkt etc.). Dafür müssen Sie das Trennzeichen lediglich als zusätzliches Argument an der gewünschten Stelle der Formel eintippen und in Anführungszeichen setzen. Benötigen Sie zu dem Trennzeichen zusätzlich noch ein Leerzeichen (zum Beispiel nach einem Komma oder nach einem Wort), dann fügen Sie das Leerzeichen einfach innerhalb der Anführungszeichen hinzu. Anstatt oder zusätzlich zu einem Trennzeichen können Sie optional auch noch einen zusätzlich Ergänzungstext eingeben, der dann an dieser Stelle hinzugefügt wird. In Kombination sieht das dann wie folgt aus (siehe Abb. 1).
- =VERKETTEN(B2;“ „;A2;“, wohnhaft in „;C2;“, „;D2;“ „;E2)
- =TEXTKETTE(B2;“ „;A2;“, wohnhaft in „;C2;“, „;D2;“ „;E2)
- Ergebnis: „Olaf Scholz, wohnhaft in Willy-Brandt-Straße 1, 10557 Berlin“
Möchten Sie hingegen einen Zeilenumbruch innerhalb der neuen Zielzelle erzeugen, so dass die verketteten Textinhalte untereinander in derselben Zelle stehen, dann erzeugen Sie solch einen Zeilenumbruch mit der Eingabe → ZEICHEN(10). Dieses Argument darf allerdings nicht in Anführungszeichen gesetzt werden, sonst funktioniert der Befehl nicht. Sie müssen dann zudem noch einmalig den „Textumbruch“ im Excel Menü aktivieren (in der Registerkarte → Start im Bereich → Ausrichtung; siehe die rote Umrandung in Abb. 1). Tipp: Hier finden Sie unsere detaillierte Anleitung mit Beispielen, wie man in Excel Spalten mit Zeilenumbruch in einer Zelle zusammenführt.
- =VERKETTEN(B2;“ „;A2;ZEICHEN(10);C2;ZEICHEN(10);D2;“ „;E2)
- =TEXTKETTE(B2;“ „;A2;ZEICHEN(10);C2;ZEICHEN(10);D2;“ „;E2)
- Das Ergebnis wird dann korrekt nebeneinander mit Leerzeichen und untereinander mit Zeilenumbruch ausgegeben:
| Olaf Scholz Willy-Brandt-Straße 1 10557 Berlin |
Wie man die Trennzeichen in die Excel-Formel TEXTVERKETTEN eingibt:
Im Unterschied zu den beiden Verkettungs-Formeln oben müssen Sie bei TEXTVERKETTEN das Trennzeichen nur einmal eingeben. Und zwar gleich als erstes Argument zu Beginn der Formel. Als zweites Argument legen Sie dann die „Leer_ignorieren“ Option fest (auf Englisch: IgnoreEmpty). Wenn Sie für „Leer_ignorieren“ als Argument → WAHR eingeben, ignoriert Excel alle leeren Zellen in dem ausgewählten Zellbereich. Tippen Sie dafür hingegen → FALSCH ein, tauchen auch alle leere Zellen im Ergebnis auf. Das sieht dann in unserem Beispiel folgendermaßen aus.
- =TEXTVERKETTEN(„, „;WAHR;B2;A2;C2:E2)
- Ergebnis: Olaf, Scholz, Willy-Brandt-Straße 1, 10557 Berlin
3. Die Prozent- und Datumsformatierung bei der VERKETTUNG in Excel erhalten
Bei der Verkettung von Textinhalten, die über mehrere Spalten verteilt sind, geht leider der Formatcode verloren, zum Beispiel für Prozentzahlen oder das Datumsformat. Daher müssen Sie Excel in der Verkettungsformel extra anweisen, einen Zellwert auch wieder mit dem entsprechenden Formatcode auszugeben. Das funktioniert mittels des Zusatzes → TEXT(Zelle;Formatcode).
Nur durch eine Kombination der Verkettung mit dieser kurzen TEXT-Funktion kann also die ursprüngliche Formatierung von Zahlen, Datumsangaben und Uhrzeiten in Excel erhalten werden. Der Formatcode für das in Europa übliche Datumsformat in Excel lautet beispielsweise → „TT.MM.JJJJ“, für die Uhrzeit → „hh:mm“ und für Prozentzahlen → „0,0 %“. Beispiele (vergleiche ebenfalls Abb. 1, oben):
- Damit das Datum „08.12.2021“ in unserem Beispiel in der Zelle F2 beim Verketten erhalten bleibt, muss das Argument in der TEXTKETTE-Formel lauten:
=TEXTKETTE(B2;“ „;A2;“, Amtsantritt am: „;TEXT(F2;“TT.MM.JJJJ“)). - Damit eine Uhrzeit auch nach dem Verketten im Uhrzeitformat ausgegeben wird, muss das Argument in der Excel-Formel beispielsweise lauten: =TEXTKETTE(A5;TEXT(C5;“hh:mm“)).
- Damit Excel eine Prozentzahl auch nach dem Verketten wieder als Prozentzahl ausgibt, muss das Formel-Argument in unserem Beispiel lauten: =VERKETTEN(B2;“ „;A2;“, verbleibende Amtszeit: „;TEXT(G2;“0,00 %“)).
- Tipp: Die Prozentzahl wird mit so vielen Nachkommastellen angegeben, wie Sie in die TEXT-Formel eintippen (bei 0% wird auf ganze Zahlen gerundet, bei 0,0% auf eine Nachkommastelle etc.).
- Eine vollständige Übersicht über aller Formatcodes in Excel finden Sie → hier im Microsoft Support.
4. Problemlösung: TEXTKETTE und VERKETTEN funktionieren nicht in meinem Excel
- Sie können in Excel „nur“ bis zu 255 Elemente mit insgesamt bis zu 8.192 Zeichen verknüpfen. Bei mehr Elementen funktionieren die drei Formeln nicht.
- Ist die auszugebende Zeichenfolge zudem länger als 32.767 Zeichen (der Maximalwert für eine einzelne Zelle), geben die drei TEXTKETTE- und VERKETTEN-Formeln den Fehler → „#WERT!“ zurück (auf Englisch: #VALUE!).
- Der Fehler → „#NAME?“ wird hingegen ausgegeben, wenn ein Trennzeichen oder ein zusätzlicher Text in der Formel nicht in die benötigten Anführungszeichen gesetzt wurde.
- Werden im Ergebnis hingegen unbeabsichtigte Anführungszeichen angezeigt, haben Sie vermutlich ein Semikolon nicht gesetzt, um aufeinanderfolgende Argumente voneinander zu trennen.
- Wenn Excel die Wörter an den Schnittstellen ohne Abstände verkettet, müssen Sie ein zusätzliches Leerzeichen einfügen.
5. Extra-Tipps: Das VERKETTEN mit anderen Excel-Formeln kombinieren
Sie können in Excel die allermeisten Formeln ganz einfach beliebig miteinander kombinieren. Dafür müssen Sie lediglich ein Argument der ersten Formel durch die andere Formel (diese dann ohne beginnendes „=“ Zeichen) ersetzen. Zum Beispiel eine VERKETTEN-WENN-Kombination, um nur zu verketten, wenn eine bestimmte Bedingung erfüllt wird (wenn mehrere Bedingungen erfüllt werden sollen, wäre das dann nicht die WENN-, sondern die WENNS-Funktion). Wie Sie Formeln in Excel miteinander kombinieren, haben wir Ihnen im folgenden Beitrag am Beispiel der Kombination einer WENN-Funktion mit zwei SVERWEIS-Funktionen zusammengefasst.
- Wie funktioniert die Wenn-Dann-Funktion in Excel? Mit mehreren Bedingungen, verschachtelt und in Kombination mit anderen Formeln?
- Alle Anleitungen zu den Microsoft 365-Tools: Alle Tipps & Tricks zu den einzelnen Office-Apps auf einen Blick, z. B. zu Excel, PowerPoint, Outlook, Teams, Planner & Co.
- Kostenloses Download-Center: Hier finden Sie weitere praxiserprobte Anleitungen, Checklisten und Vorlagen für eine effizientere Organisation und Zusammenarbeit im Büro!
Büro-Kaizen Video-Tutorial: Die Top 3 Excel-Formeln
(Dauer 14:48 Minuten)
