Wie kann man Zellen in Excel verketten und dabei leere Zellen ignorieren oder überspringen?

Die Verkettungsfunktion von Excel kann Ihnen helfen, mehrere Zellenwerte schnell in eine Zelle zusammenzuführen. Wenn es jedoch leere Zellen innerhalb der ausgewählten Zellen gibt, werden diese ebenfalls in die Verkettung einbezogen. Manchmal möchten Sie jedoch nur Zellen mit Daten verketten und leere Zellen überspringen. Wie können Sie das in Excel erreichen?

Zellen verketten und dabei leere Zellen ignorieren oder überspringen mit einer Formel

Zellen verketten und dabei leere Zellen ignorieren oder überspringen mit einer benutzerdefinierten Funktion

Zellen verketten und dabei leere Zellen ignorieren oder überspringen mit Kutools für Excel

Zellen verketten und dabei leere Zellen ignorieren oder überspringen mit einer Formel

Angenommen, ich habe die folgenden Zellendaten, die teilweise leere Zellen enthalten. Um die Zeilen in eine Zelle zu kombinieren, können Sie eine Formel anwenden, um dies zu lösen.

Bitte geben Sie diese Formel in eine leere Zelle ein, in der Sie das verkettete Ergebnis platzieren möchten: =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,""), und ziehen Sie den Ausfüllkursor nach rechts in die Zellen, auf die Sie diese Formel anwenden möchten. Die Werte der Zeilen wurden ohne die leeren Zellen kombiniert, wie im folgenden Screenshot gezeigt:

Tipp: Wenn weitere Zeilen verkettet werden müssen, sollten Sie die IF-Funktion verwenden, um die Zellen zu verbinden, z. B. =A1&IF(A2<>"","-"&A2,"")&IF(A3<>"","-"&A3,"")&IF(A4<>"","-"&A4,"")&IF(A5<>"","-"&A5,"")&IF(A6<>"","-"&A6,""). Das "–"-Zeichen in der Formel kann durch beliebige andere Trennzeichen ersetzt werden, die Sie benötigen.

Zellen verketten und dabei leere Zellen ignorieren oder überspringen mit einer benutzerdefinierten Funktion

Wenn mehrere Zellen kombiniert werden müssen, wird die obige Formel zu komplex auszuführen sein. Daher können Sie die folgende benutzerdefinierte Funktion verwenden, um dieses Problem zu lösen.

1. Halten Sie die Tasten Alt + F11 in Excel gedrückt, um das Fenster Microsoft Visual Basic for Applications zu öffnen.

2. Klicken Sie auf Einfügen > Modul und fügen Sie das folgende Makro im Modulfenster ein.

VBA-Code: Zellen verketten und dabei leere Zellen ignorieren:

Function Concatenatecells(ConcatArea As Range) As String
'updateby Extendoffice
  For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & "/"): Next
  Concatenatecells = Left(nn, Len(nn) - 1)
End Function

3. Speichern und schließen Sie dieses Codefenster, gehen Sie zurück zum Arbeitsblatt und geben Sie dann diese Formel ein: =concatenatecells(A1:A5) in eine leere Zelle, und ziehen Sie den Ausfüllkursor nach rechts in die Zellen, auf die Sie diese Formel anwenden möchten. Sie erhalten das folgende Ergebnis:

Tipp: Im obigen VBA-Code können Sie das „/“-Zeichen durch beliebige andere Trennzeichen ersetzen, die Sie benötigen.

Zellen verketten und dabei leere Zellen ignorieren oder überspringen mit Kutools für Excel

Abgesehen von der langweiligen Formel und dem Code kann ich Ihnen ein praktisches Tool empfehlen – Kutools für Excel. Mit seinem leistungsstarken Kombinationsdienstprogramm können Sie mehrere Zellen mühelos in eine einzige Zelle verketten.

Nach der Installation von Kutools für Excel gehen Sie bitte wie folgt vor:

1. Wählen Sie die Zellenwerte aus, die Sie kombinieren möchten.

2. Klicken Sie auf Kutools > Zusammenführen & Aufteilen > Zeilen/Spalten/Zellen zusammenführen und Werte beibehalten, siehe Screenshot:

3. Im erscheinenden Dialogfeld:

• (1.) Wählen Sie den Typ der Zellen aus, die Sie kombinieren möchten. Sie können Spalten, Zeilen oder alle Zellen in eine einzelne Zelle kombinieren;
• (2.) Geben Sie ein Trennzeichen für Ihre verketteten Inhalte an;
• (3.) Wählen Sie den Ort für das Ergebnis aus. Sie können das Ergebnis in die oberste oder unterste Zelle platzieren;
• (4.) Legen Sie fest, wie mit den kombinierten Zellen verfahren werden soll. Sie können die Inhalte aus den kombinierten Zellen behalten oder löschen, und Sie können diese kombinierten Zellen auch zusammenführen.

4. Nachdem Sie die Einstellungen abgeschlossen haben, klicken Sie auf die Schaltfläche OK, und die ausgewählten Zeilen wurden jeweils in eine Zelle kombiniert, wobei die leeren Zellen automatisch übersprungen wurden, siehe Screenshot:

Jetzt Kutools für Excel herunterladen und kostenlos testen!

Demo: Zellen verketten und dabei leere Zellen ignorieren oder überspringen mit Kutools für Excel