4 Wege in Excel, um Texte zu verketten 32

Artikelbild-175
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 mit Excel 2019 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 2016 und älter leider darauf verzichten.

 

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

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

  • Avatar-Foto
    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

  • Avatar-Foto
    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

    • Avatar-Foto
      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

      • Avatar-Foto
        Martin Weiß

        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

  • Avatar-Foto
    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$.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Thorsten Daum

    Hallo Martin,

    ich habe Excel 2016 aber leider geht diese Funktion nicht. Laut dem office support geht das erst mit der Version 2019 … gibt es bei 2016 einen Trick oder hast Du Dich vertippt?

    Gruß Thorsten

    • Avatar-Foto
      Martin Weiß

      Hallo Thorsten,

      nein, ich habe mich nicht vertippt. Aber zu dem Zeitpunkt, an dem der Artikel entstanden ist war mir nicht bewusst, dass die beiden letztgenannten Funktionen nur im Rahmen von Office 365 verfügbar waren (was zu der Zeit im Wesentlichen Excel 2016 entsprach). Tut mir leid.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Claudia

    Hi Martin, mega Tipp, Deine Variante 4 war genau das, was ich gesucht habe und es hat einfach perfekt geklappt 🙂 Ich wusste doch, dass Excel sowas können muss, aber ich habs nicht gefunden wie. Dank Google und Deiner Seite nun schon und ganz extrem viel Arbeit wurde mir somit erspart. Tolle Seite, ich danke Dir 🙂 Liebe Grüße Claudia.

  • Avatar-Foto
    ax

    Lieber Martin,
    mega cool deine anleitungen und Excel-Lösungen.
    Getraue beinahe nicht zu fragen:
    Ich würde gerne von einem Tab1 (sagen wir DAS DATUM und noch EINE BESTELLNUMMER)
    auf dem nächsten Tab2 automatisch oben einfügen oder verbinden, so dass ich beim Print
    diese einfache Textübernahme automatisch habe.
    Mein Excel will nicht gehorchen, habe alle Zellenformatierungen hin und her gemacht, es kommt
    immer eine Fehler-/Formelangabe aber eben nicht der ANGEZEIGTE INHALT aus Tab1?
    Bitte lach mich nicht aus. Besten Gruss Ax

    • Avatar-Foto
      Martin Weiß

      Hallo ax,

      keine Angst, hier wird niemand ausgelacht 😉
      Aber wenn du noch schreiben würdest, mit welcher Formel du es versucht hast, bei der der Fehler auftritt, wäre ein konkreter Tipp etwas einfacher.

      Ich versuche es daher mal allgemein: Wenn die Werte aus 2 Zellen kombiniert werden sollen, dann geht das mit dem &-Zeichen. Also beispielsweise
      =A1&A2
      oder
      =Tabelle1!A1&Tabelle1!A2

      Wenn die Zelle ein Datum enthält, dann wird unter Umständen nur der numerische Wert angezeigt, also z.B. 43995 für den 13.06.2020. In diesem Fall musst du zusätzlich die TEXT-Funktion verwenden. Beispiel:
      =TEXT(A1;“TT.MM.JJJJ“)

      Vielleicht hilft das ja weiter.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Helga Maria Gruber

    Hallo Martin,

    ich möchte in einer Tabelle die Spalten E „Nachname“ und F „Vorname“ zusammenführen und habe mich schon über die Formel =E2&“ „&F2 gefreut. Wenn ich aber die ursprünglichen beiden Spalten lösche, ist natürlich das Ergebnis auch weg – no-na.
    Gibt es eine andere Möglichkeit außer sich, wie Du es nennst, einen Wolf zu tippen? Lässt sich das Ergebnis der Formel irgendwie in den „Zieltext“ umwandeln?

    Danke und liebe Grüße
    Helga

  • Avatar-Foto
    Helga Maria Gruber

    Hallo Martin,

    Ergänzung zu meinem vorigen Mail: ich bin selbst auf die Lösung gekommen 🙂
    zuerst mit der Formel die Zellen zusammenführen, dann neue leere Spalte einfügen, die zusammengeführte hineinkopieren und nur Inhalte übernehmen. Juhuu, es klappt!

    Liebe Grüße
    Helga

    • Avatar-Foto
      Martin Weiß

      Hallo Helga,

      freut mich, dass du eine Lösung gefunden hast. Das wäre auch genau meine Antwort gewesen 🙂
      Du musst übrigens keine zusätzliche Spalte einfügen, sondern kannst nach dem Kopieren die Werte auch direkt wieder über die Formeln einfügen.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Alois Metzger

    Hi Martin,
    habe ein Problem mit der Verkettung & gezieltem & selektivem Textumbruch von Texten aus verscheiden Zellen.
    Gegeben Bsp.:
    Zelle A8: ‚-QM1.1 (Formatierung Standard)
    Zelle K8: ‚-MB61.1:4

    =VERKETTEN(A8;ZEICHEN(10);K8;ZEICHEN(10))
    Das Ergebnis bei aktivem Textumbruch ist wie folgt [Darstellung einer Zelle]
    [Absatz 1]-QM1.1
    [Absatz 2]-MB61.1
    [Absatz 3]:4 —> rutscht immer nach [Absatz 3]; watum
    Jedoch möchte ich dass das „:4“ direkt am Ende von [Absatz 2] verbleibt?
    Wie kann ich das in einer Formel steuern?
    Danke im Voraus
    Alois

    • Avatar-Foto
      Martin Weiß

      Hallo Alois,

      ich erkenne jetzt ehrlich gesagt auch keinen Grund, warum :4 bei Dir mit dieser Formel in eine eigene Zeile rutscht. Nur zur Sicherheit: Es könnte nicht an der Spaltenbreite liegen, die vielleicht zu schmal gewählt ist?

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Wolfram Herrmann

    Lieber Martin, ich habe für eine wissenschaftliche Arbeit etwa 4000 Wortpaare Deutsch-Englisch, die miteinander verwandt sind. Diese stehen in 4 Spalten nebeneinander, zwei Spalten Schrift De/Britisches Englisch, eventuell auch noch eine separate Spalte Amerikanisches Englisch;das gleiche auf der Lautseite Lautschrift De/Britisches Englisch, eventuell Amerikanisches Englisch. Diese Tabellen möchte ich mit TextVerketten vergleichen. Auf Schriftebene ergeben sich dabei andere Unterschiede als auf Lautebene.

    Ziel ist, die Wörter in ihre Schrift- und Lautbestandteile zu zerlegen. Doppellaute wie sollen zusammenbleiben. Wenn Doppelzeichen wie zu Einzelzeichen werden, soll dies nur als eine Veränderung gelten, also nach <b> Danach wollte ich die einzelnen Bestandteile in Diagrammen darstellen und sortieren, beispielsweise auf der Schriftseite <b> vs. wie in Liebe-love, Taube-dove, geben-give, leben-live. Auf der Lautseite ergeben sich andere Sortierungen.Dazu muss ich wahrscheinlich Hilfstabellen anlegen und verknüpfen, damit ich eine Nomenklatur habe, die Excelumsetzen kann.

    Gibt es eine simple Methode, die Worte mit TEXT VERKETTEN zu vergleichen und automatisiert in die einzelnen Bestandteile zu zerlegen? So dass man das nicht von Hand machen muss?

    Beispiel

    abbot æbət Abt a:pt
    accent əksənt Akzent akt͡sɛnt

    Im Beispiel kommt also folgendes heraus:
    Schrift bleibt gleich, aber Lautebene [æ-a]
    Ersetzung Schrift, auf Lautebene [p] vs. [b]
    fällt in Richtung Deutsch weg, [ə] fällt im Deutschen ebenfalls weg, muss umgekehrt aber hinzugefügt werden.
    ist in beiden Sprachen gleich Schrift, aber auch gleich als Laut [t]

    Herzlichen Gruss aus Berlin!

    Wolfram

    • Avatar-Foto
      Martin Weiß

      Hallo Wolfram,

      ich muss leider gestehen, dass ich nicht ganz schlau werde aus deinen Ausführungen und mir daher auch nicht ganz klar ist, was genau herauskommen soll… ?
      So oder so: Mit TEXTVERKETTEN lassen sich die einzelnen Elemente nur verketten, also zusammenführen. Aber eben nicht trennen.

      Tut mir sehr leid, wenn ich nicht weiterhelfen kann.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Thomas

    Hallo Martin,

    Danke für deine übersichtliche Darstellung aller Verkettungsbefehle. Da hab ich noch was gelernt. Hab jetzt aber ein anderes Problem bei Textketten. Ich Verknüpe Bauvorhaben, Bauort und Bauherr. Da die Texte unterschiedlich lang sind möchte ich Platzsparend arbeiten. Ich holle die werde von einen Anderen Tabellenblatt. Die Form schaut dann wie folgend aus:
    TEXTKETTE(‚2021′!C2;“
    „;’2021′!G2;“,
    Bauherr: „;’2021‘!D2)

    Es kommt also in der Zelle ein Ergebniss über mehrere Zellen. Nur der Automatische Textumbruch funktioniert nicht. Ich muss immer von Hand den Textumbruch einmal deaktevieren und dann aktevieren. Google war leider nicht mein Freund und hat mir keine Ergebnisse gelifert.
    Ich verwende zurzeit Excel 2019

    • Avatar-Foto
      Martin Weiß

      Hallo Thomas,

      ich habe das bei mir gerade mal nachgestellt und verstehe, was du meinst. Leider habe ich für dieses Verhalten in Excel auch keine Lösung parat. Auch bei mir wird der Umbruch erst angezeigt, wenn ich ihn einmal deaktiviere und dann wieder aktiviere.
      Vielleicht hat ja ein anderer Leser eine Idee?

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Thomas B

    Erstmal danke für die Zusammenfassung.

    Ich versuche eine Matchcode zu generieren. Hier habe ich verschiedene Daten die ich eingebe und dann lasse ich mir einen Code generieren. Hier ist jedoch das Problem, dass ich nicht immer Werte habe und dadurch entstehen dann 0 im Matchcode die ich nicht möchte. Ich habe bei den Formeln davor überall bei 0 die Werte ausblenden lassen, was aber jetzt bei dem Verketten nicht funktioniert. Hat hier jemand eine Lösung?

    Gruß Thomas B

    • Avatar-Foto
      Martin Weiß

      Hallo Thomas,

      wenn ich es richtig verstehe, referenzierst du Formelzellen, die eben manchmal 0 ergeben.
      In diesem Fall helfen vermutlich nur WENN-Funktionen. Beispiel für die Verkettung von A4, A5 und A6:
      =WENN(A4<>0;A4;"")&WENN(B4<>0;B4;"")&WENN(C4<>0;C4;"")

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Julius

    Hallo zusammen!

    Ich bin erst kürzlich über diesen Blog gestolpert und war ganz begeistert, weil ich TEXTKETTE und TEXTVERKETTEN bislang nicht kannte. Auf den ersten Blick dachte ich, dass mir das nich so viel Nutzen bringt, weil ich selten ganze Bereiche miteinander verketten muss, heute allerdings hat es sich als Segen erwiesen.

    Ich wollte gern nach einer Auftragsnummer die zugehörigen Umsätze aus zwei verschiedenen Datenquellen darstellen und vergleichbar machen. Da ich die Auftragsnummern per EINDEUTIG-FILTER-Kombination untereinander auflisten lasse, war die Idee, die Umsätze untereinander in einer Zelle auflisten zu lassen. Und genau hierfür war die TEXTVERKETTEN klasse!

    Auch hier habe ich FILTER verwendet, um die Umsätze zur zugehörigen Auftragsnummer zu erhalten. Als Trennzeichen habe ich mittels ZEICHEN(10) einen Absatz verwendet. Nun musste nur noch im Start-Register der Textumbruch aktiviert werden, und schon befüllt sich die Zelle zeilenweise mit allen vorhandenen Umsatzpositionen – und falls aus der anderen Datenquelle weniger oder mehr Positionen enthalten sind, werden sie trotzdem in nur einer benachbarten Zelle dargestellt und sind somit leicht vergleichbar!

    Hier die Formel:

    =TEXTVERKETTEN(ZEICHEN(10);;FILTER(Umsatzspalte;Auftragsnummerspalte=Auftragsnummer_aus_Liste))

    Funktioniert mit allen Arrays! (Vermutlich aber nur bis zu 32.767 Zeichen 😉 )

    • Avatar-Foto
      Martin Weiß

      Hallo Julius,

      das ist ein schönes Beispiel und eine clevere Kombination der neuen Funktionen. Danke für’s Teilen hier!

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Daniel

    Hallo Martin,

    ich habe eine kleine Ergänzung zu deinem Beitrag.
    Wissenswert für alle, die schon mal nach einer Möglichkeit gesucht haben, Positionsnummern mit definierter Ordnungszahl zu erstellen, die sich nicht zerschießt, falls noch eine Zeile eingefügt wird.
    Beispiel:
    Gewünschte OZ-Maske 00.00.000
    Das geht mit der neuen Funktion wunderbar!
    z.B.:
    =WENN([@E1]=““;““;TEXTVERKETTEN(„.“;FALSCH;TEXT([@E1];“00″);TEXT([@E2];“00″);TEXT([@E3];“000″)))

    Grüße D.

  • Avatar-Foto
    Ralf Burmeister

    Hallo Martin,
    Was für super Erklärungen. Vielen Dank es war sehr hilfreich!!

    Trotzdem noch eine Frage:
    Ich habe mich für Textverketten entschieden und es sieht sehr gut aus.
    Ist es möglich den Text der einzelnen verketteten Texte jeweils mit einem Zeilenumbruch der einzelnen Zellen zu bekommen?
    Beispiel soll die Zellen zeigen
    A B C D E
    1 IRB 1400 Drehwender Verfahrbahn 10m Sicherheitszaun 6

    Ergebnis
    IRB 1400
    Drehwender
    Verfahrbahn 10m
    Sicherheitszaun
    6

    (konnte kein Bild einfügen aber oben sollen es die einzelnen Zellen sein und das Ergebnis dann in einer Zelle)

    Viele Grüße
    Ralf

    • Avatar-Foto
      Joachim Kromm

      Hallo Ralf,
      das geht, indem ZEICHEN(10) für den Zeilenumbruch mit verkettet wird:
      =A1 & ZEICHEN(10) & B1…
      In der Menüleiste „Start“ muss dann noch der Zeilenumbruch aktiviert sein.
      Gruß Joachim