Excel-Funktion Verbinden()

Diese benutzerdefinierte Funktion setzt die Inhalte von gefüllten Felder eines festgelegten Bereichs zusammen und fügt jeweils ein Trennzeichen ein.

Anforderung:

Aus mehreren (nicht immer gefüllten) Spalten soll eine Kapitelnummerierung (z. B. 1. bis 1.2.3.4.) zusammengesetzt werden.

Lösung:

Mit VERKETTEN und vielen WENNS oder mit einem geschickten Einsatz von VERKETTEN, GLÄTTEN und WECHSELN wäre das Problem zu lösen. Aber jede kleine Änderung (z. B. eine andere Spaltenanzahl) ist mit viel Handarbeit verbunden und fehleranfällig. Auf Dauer einfacher und flexibler ist eine kleine benutzerdefinierte Funktion. Mit ihr lassen sich z. B. auch IP-Adressen oder Artikelnummern zusammensetzen.

So sieht die fertige Funktion aus:

Function Verbinden(Bereich As Range, Optional Trennzeichen As String = ".", _
                   Optional Zeichen_am_Ende As Boolean = True) As String
'==================================================================================
' Eine benutzerdefinierte Funktion aus Raphael Heins Da|ten|bank|Ma|nu|fak|tur
'
' Benutzung:    Eingabe im Tabellenblatt "=Verbinden(A1:K1;"-";WAHR)
' Funktion:     Verkettet die gefüllten Felder und fügt ein Trennzeichen ein, das
'               auf Wunsch auch am Ende angehängt bzw. weggelassden wird
'
' Parameter:    - Bereich (ein einzeiliger, zusammenhängender Bereich beliebiger
'                 Länge, z. B. A1:F1)
'               - Trennzeichen (ein oder mehrere Zeichen, in Anführungszeichen,
'                 z. B. "-", optional, Standardwert ".")
'               - Trennzeichen am Ende ausgeben? (WAHR oder FALSCH, optional,
'                 Standardwert WAHR)
'
' Autor:        Raphael Hein
' Datum:        01.07.2014
'==================================================================================
   Dim strText As String
   Dim i As Integer
   
   ' Funktion immer neu berechnen, wenn sich etwas im Tabellenblatt ändert
   Application.Volatile
   
   ' Der Bereich, der verbunden wird, muss zusammenhängend in einer Zeile stehen
   If Bereich.Rows.Count > 1 Or Bereich.Areas.Count > 1 Then
      Verbinden = "#BEREICH!"
   Else
      ' Texte und Trennzeichen zusammensetzen
      For i = 1 To Bereich.Columns.Count
         strText = Cells(Bereich.Row, Bereich.Column + i - 1)
         ' nur Zellen verwenden, die nicht leer sind
         If strText <> "" Then Verbinden = Verbinden & strText & Trennzeichen
      Next i
      ' Ggf. ein (oder mehrere) letzte Trennzeichen entfernen
      If Not Zeichen_am_Ende Then
         Verbinden = Left(Verbinden, Len(Verbinden) - Len(Trennzeichen))
      End If
   End If
   
End Function

Diese Funktion arbeitet natürlich auch in Excel:mac.

Sie wissen nicht, was eine benutzerdefinierte Funktion ist und wie man sie erstellt? Dann erkläre ich es Ihnen hier kurz.

Download

Verbinden() für Excel 2007 (und neuer)
(Datei: Verbinden.zip, ca. 23 KB)

Nachtrag

Seit Excel 2019 bzw. in Excel für Office 365 gibt es die neue Funktion TEXTVERKETTEN , die ähnliches kann. Das ist eine Alternative, wenn man diese Version hat und auf Makros verzichten will oder muss.