Verketten bis zur nächsten Leeren Zeile/ Zelle

Forumthread: Verketten bis zur nächsten Leeren Zeile/ Zelle

15.03.2007 13:12:00

Andreas

Hallo liebe Excelfans auf Herber.de,
ich arbeite mich gerade in ein neues Themengebiet ein und habe ein Glossar dazu gefunden, welches in Excel abgefasst ist. Aber die Erläuterungen zu einem Begriff sind in der nachfolgenden Spalte nicht in einer Zelle, sondern in mehreren Zellen untereinander geschrieben. Diese möchte ich in einer Zelle zusammenfassen. Die Länge der Erläuterungen (d.h. der verwendeten Zeilen bis zum nächsten Begriff) sind nicht konstant.
Von der Logik her:
Wenn Du mind. zwei nichtleere Zeilen/ Zellen untereinander findest, dann verkette diese. Wenn es mehr als zwei sind, dann verkette alle bis zur nächste leeren Zeile/ Zelle.
Beispieldatei mit Kommentaren habe ich angehangen.
https://www.herber.de/bbs/user/41103.xls
Ich habe schon viel in diesem und anderen Foren gesucht, aber nichts gefunden. Vielleicht war ich schon nah dran, aber ich habe es nicht lösen können. Meine Ideen waren mit der Funktion Index oder Indirekt oder Bereich Verschieben (?) zu arbeiten. Oder auch mit einer Array Formel? Die einfachste Formel wäre eine geschachtelte wenn Funktion: Wenn B7 und B8 ungleich leer, dann verkette B7 und B8; Wenn B7 und B8 und B9 ungleich leer, dann verkette B7, B8 und B9 etc.
Aber das ist nicht elegant und wird auch durch die maximale Verschachtelungstiefe reglementiert.
Mit einem Makro würde es vermutlich relativ einfach gehen. Leider bin ich mit Makros nicht so bewandert. Und irgendwie habe ich den Ehrgeiz es mit Formeln zu lösen! Ich bin mir sicher, daß es geht.
Vom reinen Arbeitsaufwand her wäre ich manuell schon 5mal fertig und hätte jetzt noch eine Mittagspause, aber da ist diese Neugier Neugier was alles möglich ist!
Ich würde mich freuen, wenn ich jemanden mit dieser Neugier anstecke und anhand seiner Formel wieder etwas lernen kann.
Gruß aus Berlin,
Andreas Hanisch

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Verketten bis zur nächsten Leeren Zeile/ Zelle

ransi

HAllo
Vielleicht war ich schon nah dran, aber ich habe es nicht lösen können.
Tröste dich.
Das Problem haben schon Generationen von Excelusern vor dir versucht zu lösen.
Sie sind alle gescheitert.
Verketten von Zellen über Zellbereiche via Formel geht nur mit Verketten() oder mit &.
ransi

AW: Verketten bis zur nächsten Leeren Zeile/ Zelle

15.03.2007 18:22:05

Erich

Hallo Andreas,
in der Mappe sind auch Module - dann kommt wohl auch VBA in Frage. Probier mal:


Option Explicit
Sub VerketteBinC()
Dim lngB As Long, zB As Long, zC As Long, strT As String
lngB = Cells(Rows.Count, 2).End(xlUp).row
zB = 1
Do
While IsEmpty(Cells(zB, 2))
zB = zB + 1
Wend
zC = zB
While Not IsEmpty(Cells(zB, 2))
If strT > "" Then strT = strT & " "
strT = strT & Trim(Cells(zB, 2))
zB = zB + 1
Wend
Cells(zC, 3) = strT
strT = ""
Loop Until zB >= lngB
End Sub

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

AW: Verketten bis zur nächsten Leeren Zeile/ Zelle

22.03.2007 19:30:00

Andreas

Hallo Erich,
es tut mir leid, daß ich erst nun atworte. Es war eine Menge zu tun im Büro und ich wollte Dein Makro nicht zwischen Tür und Angel testen. Es funktioniert sehr gut. Es gab keine Fehlermeldung, es waren keine Anpassungen notwendig. Und für die relativ hohe Komplexität der Aufgabe ist es erstaunlich kurz und übersichtlich. Respekt.
Einzig die Semikola, die am Ende mancher, zu verschmelzender Zelleinträge stehen werden nicht gelöscht. Aber das ist ein Detail...
Ich bedanke mich recht herzlich bei Dir und wünsche Dir noch einen angenehmen Abend.
Grüße aus Berlin nach Kamp-Lintfort,
Andreas

Infobox / Tutorial

Verketten von Zellen bis zur nächsten leeren Zeile in Excel

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um Zellen in Excel zu verketten, bis du auf eine leere Zelle stößt, kannst du folgende Schritte durchführen:

Vorbereitung: Stelle sicher, dass deine Daten in einer Spalte angeordnet sind. Zum Beispiel in Spalte B.
Makro erstellen: Drücke ALT + F11, um den VBA-Editor zu öffnen. Klicke auf Einfügen > Modul, um ein neues Modul zu erstellen.

Code einfügen: Kopiere den folgenden VBA-Code in das Modul:

Option Explicit
Sub VerketteBinC()
    Dim lngB As Long, zB As Long, zC As Long, strT As String
    lngB = Cells(Rows.Count, 2).End(xlUp).Row
    zB = 1
    Do
        While IsEmpty(Cells(zB, 2))
            zB = zB + 1
        Wend
        zC = zB
        While Not IsEmpty(Cells(zB, 2))
            If strT > "" Then strT = strT & " "
            strT = strT & Trim(Cells(zB, 2))
            zB = zB + 1
        Wend
        Cells(zC, 3) = strT
        strT = ""
    Loop Until zB >= lngB
End Sub

Makro ausführen: Schließe den VBA-Editor und gehe zurück zu Excel. Drücke ALT + F8, wähle VerketteBinC aus und klicke auf Ausführen.
Ergebnis überprüfen: Die verketten Ergebnisse werden in der danebenliegenden Spalte (z.B. Spalte C) angezeigt.

Häufige Fehler und Lösungen

Keine Ergebnisse in Spalte C: Stelle sicher, dass die Daten in Spalte B korrekt eingegeben sind und dass das Makro nicht auf leere Zellen stößt, bevor es eine Verkettung durchführt.
Semikolons am Ende: Wenn du am Ende der verketteten Zellen ein Semikolon siehst, kannst du den Code anpassen, um unerwünschte Zeichen zu entfernen. Füge dazu eine Bedingung ein, die das Semikolon entfernt, wenn es am Ende steht.

Alternative Methoden

Falls du keine VBA-Makros verwenden möchtest, kannst du auch eine Array-Formel nutzen, um Zellen zu verketten:

Wähle eine Zelle, in der du das Ergebnis haben möchtest.
Gib folgende Formel ein (ersetze B1:B10 mit deinem Zellbereich):
```
=TEXTJOIN(" "; WAHR; B1:B10)
```
Drücke STRG + UMSCHALT + ENTER, um die Array-Formel zu aktivieren.

Diese Methode funktioniert in Excel 365 und Excel 2019.

Praktische Beispiele

Angenommen, du hast folgende Daten in Spalte B:

B
Apfel
Banane
(leer)
Orange
Kiwi
(leer)

Nach Ausführung des Makros oder der Array-Formel könnte das Ergebnis in Spalte C so aussehen:

C
Apfel Banane
Orange Kiwi

Tipps für Profis

Nutze den WENN-Befehl, um die Verkettung nur durchzuführen, wenn die Zellen nicht leer sind. Beispiel:
```
=WENN(B1<>""; B1 & " " & WENN(B2<>""; B2; ""); "")
```
Experimentiere mit dem TEXTJOIN-Befehl, um mehrere Bereiche gleichzeitig zu verketten. Dies kann die Arbeit mit Excel-Spalten und -Bereichen erheblich erleichtern.

FAQ: Häufige Fragen

1. Wie kann ich die Verkettung auf mehrere Spalten anwenden?
Du kannst die oben genannten Techniken für jede Spalte wiederholen, indem du die entsprechenden Zellbereiche anpasst.

2. Ist die Verwendung von VBA die einzige Möglichkeit?
Nein, du kannst auch Array-Formeln oder die TEXTJOIN-Funktion verwenden, wenn du eine neuere Excel-Version hast.

3. Was mache ich, wenn ich leere Zellen innerhalb des Bereichs habe?
Das Makro ignoriert leere Zellen, solange es die Verkettung nicht unterbricht. Bei Array-Formeln kannst du die WAHR-Bedingung in TEXTJOIN verwenden, um leere Zellen zu ignorieren.

Beliebteste Forumthreads (12 Monate)

Verketten bis zur nächsten Leeren Zeile/ Zelle

Forumthread: Verketten bis zur nächsten Leeren Zeile/ Zelle

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Verketten von Zellen bis zur nächsten leeren Zeile in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Verketten bis zur nächsten Leeren Zeile/ Zelle

Forumthread: Verketten bis zur nächsten Leeren Zeile/ Zelle

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Verketten von Zellen bis zur nächsten leeren Zeile in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread