4 Wege in Excel, um Texte zu verketten 8

Verschiedene Wege, mehrere Zellen miteinander zu verknüpfen.
 

Excel ist nicht nur gut im Umgang mit Zahlen, sondern bietet auch viele praktische Funktionen im Umgang mit Texten. Ein Teilbereich davon ist das Verketten von Texten oder das Verknüpfen von verschiedenen Zellen.

Wozu man so etwas braucht? Zum Beispiel, um aus den einzelnen Feldern einer Adressentabelle einen einzigen Datensatz zu bilden.

Im heutigen Artikel zeige ich dir 4 verschiedene Möglichkeiten, wie man in Excel Texte bzw. Zellen miteinander verketten kann.

Und so geht’s:

Variante 1: Das &-Zeichen

Mit dem Ampersand-Zeichen (&) lassen sich die Inhalte verschiedener Zellen miteinander verknüpfen. Alle zu verkettenden Zellen werden durch das &-Zeichen verbunden. Anstatt einer Zelladresse kann auch ein feststehender Text angegeben werden, der in doppelte Anführungszeichen gesetzt wird:

Variante 1: Das Ampersand-Zeichen

Variante 1: Das Ampersand-Zeichen

Variante 2: Die VERKETTEN-Funktion

Im Unterschied zur Variante mit dem &-Zeichen werden hier die einzelnen Zelladressen durch ein Semikolon getrennt. Auch hier können feste Texte wieder mit doppelten Anführungszeichen angegeben werden:

Variante 2: Die VERKETTEN-Funktion

Variante 2: Die VERKETTEN-Funktion

Tipp:
Wenn ausschließlich Zelladressen eingegeben werden, kann man sich viel Tipperei sparen. Einfach nach der ersten Klammer die STRG-Taste drücken und gedrückt halten und nacheinander alle gewünschten Zellen anklicken. Excel setzt das Semikolon dann automatisch.

Variante 3: Die TEXTKETTE-Funktion

Diese Funktion wurde mit Excel 2016 neu eingeführt und ist in älteren Excel-Versionen leider nicht verfügbar. Diese Funktion spielt ihre Stärke aus, wenn ausschließlich Zelladressen für die Verknüpfung verwendet werden. Denn hier kann man komplette Zellbereiche markieren und muss nicht, wie bei der VERKETTEN-Funktion, einzelne Zellen angeben:

Variante 3: Die TEXTKETTE-Funktion

Variante 3: Die TEXTKETTE-Funktion

Der Nachteil: Wenn zwischendurch feste Texte als Trennzeichen benötigt werden, muss man doch wieder ein wenig mehr Tipparbeit leisten.

Variante 4: Die TEXTVERKETTEN-Funktion

Das Highlight habe ich mir natürlich bis zum Schluss aufgehoben: Die ebenfalls mit Excel 2016 eingeführte Funktion TEXTVERKETTEN:
=TEXTVERKETTEN(Trennzeichen; Leere ignorieren; Text1; Text2....)

Hier hat man also die Möglichkeit, einmalig ein Trennzeichen anzugeben, welches dann automatisch zwischen alle Zellen gesetzt wird. Über den zweiten Parameter steuert man, ob leere Zellen ignoriert werden sollen (Parameter = WAHR bzw. Parameter wird weggelassen) oder ob auch leere Zellen verkettet werden sollen (Parameter = FALSCH):

Variante 4: Die TEXTVERKETTEN-Funktion

Variante 4: Die TEXTVERKETTEN-Funktion

Wer also in der Vergangenheit sehr viele Zellen miteinander verknüpfen musste und sich dabei einen Wolf geklickt hat, wird diese Funktion lieben. Allerdings müssen Anwender von Excel 2013 und älter leider darauf verzichten.

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

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.

8 Gedanken zu “4 Wege in Excel, um Texte zu verketten

  • Christian Fette

    Nun, unter 2010 und 2013 habe ich mir mit einer kleinen VBA-Funvtion geholfen:

    Public Function Verketten2(ByRef rngBereich As Range, strTrennzeichen As String) As String
    Dim rng As Range
    Dim strTextkette as string

    On Error GoTo Verketten2_Error
     
    For Each rng In rngBereich
    If rng „“ Then
         strTextkette = strTextkette & rng & strTrennzeichen
        End If
    Next
     
    If Len(strTextkette) > 0 Then strTextkette = Left(strTextkette, Len(strTextkette) – Len(strTrennzeichen))

    Verketten2 = strTextkette

    On Error GoTo 0
    Exit Function
     
    Verketten2_Error:
    MsgBox „Error “ & Err.Number & “ (“ & Err.Description & „) in function Verketten2()“
    End Function

  • Heinz-Jürgen Ladberg

    Danke für die Vorarbeit von Christan Fette. Seine Funktion habe ich zum Vorbild genommen und die TEXTKETTE-Funktion etwas erweitert. Optional sind außer dem Zellbereich noch drei weitere Angaben möglich.
    1. ein Separator (Zeichen oder Text) – Als Standard ist ein Semikolon voreingestellt („;“)
    2. Flag Ignore_Empty (WAHR oder FALSCH) – Wenn WAHR, werden leere Zellen unterdrückt (ist voreingestellt)
    3. Flag Direction (WAHR oder FALSCH) – Wenn WAHR, wird der Zellbereich horizontal verkettet (ist voreingestellt), wenn FALSCH, dann vertikal.

    Public Function TEXTKETTE2(ByRef rngRange As Range, Optional rngSeparator As String = „;“, Optional Ignore_Empty As Boolean = True, Optional Direction As Boolean = True) As String
    ‚ rgnRange: muss ein zusammenhängender Bereich von Zellen sein (z.B. A1:A9 oder A3:H4)
    ‚ strSeparator: Trennzeichen, das zwischen jedem Zellinhalt eingefügt wird (z.B. „; “ oder „,“ oder „?“). Hier kann jedes beliebige Zeichen verwendet werden.
    ‚ Ignore_Empty: Sollen leere Zellen Unterdrückt werden? TRUE (Standard) = ja; FALSE = nein
    ‚ Direction: Gibt die Richtung an: TRUE (Standard) = horizontal; FALSE = vertikal
    ‚ z.B. Bereich A1:E5
    ‚ 1 1 1 1 1
    ‚ 2 2
    ‚ 3 3 3
    ‚ 4 4
    ‚ 5 5 5 5 5
    ‚ =TEXTKETTE2(A1:E5) ergibt „1;1;1;1;1;1;2;2;3;3;3;4;4;5;5;5;5;5″
    ‚ =TEXTKETTE2(A1:E5;“;“) ergibt „1;1;1;1;1;1;2;2;3;3;3;4;4;5;5;5;5;5″
    ‚ =TEXTKETTE2(A1:E5;“;“;WAHR) ergibt „1;1;1;1;1;1;2;2;3;3;3;4;4;5;5;5;5;5″
    ‚ =TEXTKETTE2(A1:E5;“;“;FALSCH) ergibt „1;1;1;1;1;1;2;;;;2;3;;3;;3;4;;;;4;5;5;5;5;5″
    ‚ =TEXTKETTE2(A1:E5;“;“;WAHR;WAHR) ergibt „1;1;1;1;1;2;2;3;3;3;4;4;5;5;5;5;5″
    ‚ =TEXTKETTE2(A1:E5;“;“;FALSCH;WAHR) ergibt „1;1;1;1;1;1;2;;;;2;3;;3;;3;4;;;;4;5;5;5;5;5″
    ‚ =TEXTKETTE2(A1:E5;“;“;WAHR;FALSCH) ergibt „1;2;3;4;5;1;5;1;3;5;1;5;1;2;3;4;5″
    ‚ =TEXTKETTE2(A1:E5;“;“;FALSCH;FALSCH) ergibt „1;2;3;4;5;1;;;;5;1;;3;;5;1;;;;5;5;5;5;5;5“

    Dim strTextChain As String ‚ verkettete Texte
    Dim rngRow As Range ‚ Zeile (row)
    Dim rngColumn As Range ‚ Spalte (culumn)

    On Error GoTo Fehler ‚ Abfangen von Fehler und Sprung zur Fehlermeldung

    If Direction = True Then ‚ Direction = TRUE (horizontal)
    For Each rngRow In rngRange.Rows ‚ zuerst die Zeilen
    For Each rngColumn In rngRow.Columns ‚ und dann die Spalten
    If Not ((Ignore_Empty = True) And (rngColumn = „“)) Then ‚ Wenn nichts unterdrückt werden muss
    strTextChain = strTextChain & rngColumn & rngSeparator ‚ ergänze strTextChain um Zellinhalt und Separator
    End If ‚ sonst tue nichts
    Next ‚ nächste Spalte
    Next ‚ nächste Zeile
    Else ‚ Direction = FALSE (vertical)
    For Each rngColumn In rngRange.Columns ‚ zuerst die Spalten
    For Each rngRow In rngColumn.Rows ‚ und dann die Zeilen
    If Not ((Ignore_Empty = True) And (rngRow = „“)) Then ‚ Wenn nichts unterdrückt werden muss
    strTextChain = strTextChain & rngRow & rngSeparator ‚ ergänze strTextChain um Zellinhalt und Separator
    End If ‚ sonst tue nichts
    Next ‚ nächste Zeile
    Next ‚ nächste Spalte
    End If

    If Not strTextChain = „“ Then ‚ Wenn Text vorhanden ist
    strTextChain = Left(strTextChain, Len(strTextChain) – Len(rngSeparator)) ‚ lösche den letzen Separator
    End If

    TEXTKETTE2 = strTextChain ‚ fertigen Text übergeben

    On Error GoTo 0 ‚ Fehlermeldung zurücksetzen

    Exit Function ‚ und Schluss

    Fehler:
    MsgBox „Error “ & Err.Number & “ (“ & Err.Description & „) in function TEXTKETTE2()“

    End Function

    • Heinz-Jürgen Ladberg

      Leider funktioniert hier Copy&Paste nicht so ohne weiteres, da einige Zeichen beim Kommentar erstellen „umkodiert“ wurden. Da ist leider noch Nacharbeit von Hand nötig.
      Schade eigentlich
      Heinz-Jürgen

      • Martin Weiß Autor des Beitrags

        Hallo Heinz-Jürgen,

        vielen Dank für Deine Lösung – der Blog entwickelt sich ja langsam zur echten VBA-Fundgrube!
        Ja, Copy&Paste funktioniert hier leider nicht besonders gut und ich habe noch keinen Weg gefunden, wie man das besser hinkriegen könnte. Wer also eine Idee hat: Her damit!

        Schöne Grüße,
        Martin

  • Robert

    Leider scheint das nicht mit allen Office-Varianten zu funktionieren.
    Bei uns in der Firma haben wir Office 2016 im Einsatz und die beiden letztgenannten Funktionen fehlen leider.
    Auf der Supportpage von Microsoft steht, dass es nur mit einem 365-Abo geht. Wenn das eingetragen wird ist die Funktion dennoch nicht verfügbar.
    Komische Politik von M$.

    • Martin Weiß Autor des Beitrags

      Hallo Robert,

      ja, es stimmt leider: Excel 2016 ist nicht immer gleich Excel 2016. Die Office-365-Variante unterscheidet sich manchmal von einem Standalone-Excel-2016, was schon sehr ärgerlich sein kann.

      Schöne Grüße,
      Martin

  • Michaela Queck

    Hallo Martin, hallo Blogger, ich bin normalerweise nur mit den Grundfunktionen in Excel unterwegs (was sonst auch immer ausreichend war), hätte nun aber ein spezielles Anliegen, bei dem mir vielleicht ein „Experte“ helfen könnte. Ich schildere mal die Situation: ich habe eine Tabelle mit einer Spalte Namen und bis zu drei Standorten pro Kalenderjahr. Ich möchte nun neue Standorte für 2019 planen, an denen die einzelnen Personen jedoch noch nicht waren. D.h. wenn ich in die Zelle für Standort 1 im Jahr 2019 für die Person Müller eine Stadt schreibe, dann möchte ich, dass geprüft wird, ob die Person Müller aus Zeile 2 schon einmal in dieser Stadt war. Falls ja, soll die Textfarbe des neu geschriebenen Standortes rot sein, falls die Person noch nicht dort war soll der Text schwarz bleiben. Das soll natürlich auch für die Zellen daneben funktionieren und für alle anderen Zeilen darunter. Es befinden sich Zellen ohne Füllung in der Aufstellung, weil nicht alle Personen immer an drei Standorten pro Jahr waren. Ich hoffe ich konnte mich verständlich ausdrücken. Ich würde mich über eine Hilfestellung sehr freuen.
    Beste Grüße
    Michaela

    Name l Standort 1) 2018 Standort 2) 2018 Standort 3) 2018 l Standort 1) 2019 Standort 2) 2019 Standort 3) 2019
    ________________________________________________________________________________________________________
    Müller l Berlin München l
    Maier l Kiel Hamburg Berlin l
    Schmitt l Bremen Leipzig Kassel l
    Klein l München Bremen l

    • Martin Weiß Autor des Beitrags

      Hallo Michaela,

      schön, dass du den Weg zu meinem Blog gefunden hast. Der Kommentarbereich ist eigentlich nur für Kommentare gedacht, die sich auf den jeweiligen Blogartikel oben beziehen. Und nicht für generelle Fragen zu anderen Excel-Themen (das unterscheidet einen Blog von einem Forum).

      Eine kleine Hilfe möchte ich dir nach dieser „Ansprache“ aber trotzdem geben 🙂

      Du musst eine Regel für eine bedingte Formatierung anlegen. Markiere zuerst den Bereich mit den Zellen, die nach der Prüfung farbig werden sollen. Gehe dann über das Menü „Start | Bedingte Formatierung | Neue Regel“ und wähle den Eintrag „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.

      Ich habe das mal für eine Beispieltabelle gemacht. Die Formel hierzu würde lauten:
      =ISTTEXT(WVERWEIS(E3;$B3:$D3;1;FALSCH))

      Neue Regel eingeben

      Und das wäre dann das Ergebnis:
      Bedingte Formatierung

      Du musst natürlich die Bezüge auf deine Verhältnisse anpassen, aber damit sollte es klappen.

      Schöne Grüße,
      Martin