Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.10.2025 19:34:56
Eine Zelle in 3 Spalten teilen
20.10.2025 19:24:08
Excel Form befüllen
20.10.2025 19:03:51
wenn istleer funktioniert nicht wegen formel
20.10.2025 13:56:55
Wenn Funktion mit Xverweis oder ähnlich
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1372to1376
1372to1376
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Verketten / Concatenate über gesamte Spalte

Forumthread: Verketten / Concatenate über gesamte Spalte

Verketten / Concatenate über gesamte Spalte
11.08.2014 16:25:14
Timmie
Hallo zusammen,
ich habe folgendes Problem:
Ich habe ein Excel, bei der in Spalte A verschiedene Werte (Namen) stehen (oft auch derselbe Name mehrmals untereinander) und in Spalte B eine jeweilige Email Adresse.
Ich würde gern folgendes, banal klingendes Problem lösen, aber es erscheint doch schwieriger:
Ich würde gern in einer Ergebniszelle alle Email Adressen (in Spalte B) über den Befehl "Verketten" oder in meinem Excel "Concatenate" (englisches Excel) verbinden, wenn ein bestimmter Name in Spalte A auftaucht. In einer weteren Ergniszelle sollen dann alle Email-Adressen eines anderen Names stehen (idealerweise durch ein ";" getrennt, aber das wäre die Luxusversion).
Z.B. hat der Name "Meier" mehrere Email Adressen, die dann jeweils in B2, B3, B4 stehen. Der Name Meier steht entsprechend in A2, A3 und A4.
Leider habe ich festgestellt, dass "Concatenate" nur funktioniert, wenn man einzelne Zellen anklickt, einen Bereich kann man aber nicht markieren, der verkettet werden soll. Das macht es für mich schwierig, insbesondere wenn ich eine Matrixformel anwenden möchte, die dann nicht funktioniert.
Hat hier jemand eine Lösung (als eine Formel, ungern über mehrere Hilfsspalten)? Manuell ist keine Lösung, da es hier über mehr als 100 Zeilen laufen kann und auch Änderungen unterworfen ist.
Danke & VG,

Anzeige

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Verketten / Concatenate über gesamte Spalte
11.08.2014 16:42:54
Daniel
Hi
eine Formel die sowas kann, gibt es in Excel noch nicht und es ist auch mit den vorhandenen Excelformeln nicht so ohne weiters darstellbar, Excel hat an dieser Stelle noch eine Schwachstelle, weil es als Tabellenkalkulation halt mehr auf die Verarbeitung von Zahlen ausgerichtet als auf die von Texten, ebenso ist Excel keine Datenbank, erst recht keine relationale (so wie es hier sinnvoll wäre).
man könnte sich ein entsprechendes Verketten oder VerkettenWenn natürlich in VBA nachbauen, aber bei VBA-Nein sicherlich auch nicht die beste Variante.
am einfachsten und Speicherplatzschonensten ist immer noch die Hilfspaltenlösung mit folgender Formel in Zelle C2 (und nach unten ziehen): =Wenn(A2=A3;B2&";"&C3;B2)
beim jeweils ersten Auftauchen des Namens stehen dann alle EMailadressen in einer Zelle, so dass du die Tabelle dann mit =SVerweis("Meier";A:C;3;0) auswerten kannst.
gruß Daniel

Anzeige
Viell solltest du doch mal den RECHERCHE-...
11.08.2014 16:48:44
Luc:-?
…Button bemühen, Timmie,
denn dazu gibt's jede Menge im Archiv, zB die (in Fmln einsetzbaren) UDFs Verketten2, MxJoin u.a., auch einige Subprozeduren dazu.
VERKETTEN (Concatenate) verbindet stets nur seine explizit angegebenen Argumente miteinander, und zwar ggf elementweise, niemals die Elemente eines Arguments untereinander! So fktt nur die vbFkt Join, allerdings nur für horizontal gerichtete vbVektoren, denn nur die sind normalerweise 1dimensional.
Gruß, Luc :-?

Anzeige
Bspw findest du 'MxJoin' ...
11.08.2014 18:36:47
Luc:-?
…hier! Luc :-?

nur mit VBA oder reichlich Hilfsspalten
11.08.2014 18:04:47
ransi
Hallo Timmi
Ohne VBA wirst du da nicht zum Ziel kommen.
Teste mal dies:
Tabelle1

 ABCDEF
1MüllerImähl@irgendwas.xy1  MüllerImähl@irgendwas.xy1, Imähl@irgendwas.xy12, Imähl@irgendwas.xy16, Imähl@irgendwas.xy22
2SchmidtImähl@irgendwas.xy2  SchmidtImähl@irgendwas.xy2, Imähl@irgendwas.xy13, Imähl@irgendwas.xy17, Imähl@irgendwas.xy23
3SchneiderImähl@irgendwas.xy3  SchneiderImähl@irgendwas.xy3, Imähl@irgendwas.xy14, Imähl@irgendwas.xy18, Imähl@irgendwas.xy24
4FischerImähl@irgendwas.xy4  FischerImähl@irgendwas.xy4, Imähl@irgendwas.xy15, Imähl@irgendwas.xy19, Imähl@irgendwas.xy25
5WeberImähl@irgendwas.xy5  WeberImähl@irgendwas.xy5, Imähl@irgendwas.xy20, Imähl@irgendwas.xy26
6MeyerImähl@irgendwas.xy6  MeyerImähl@irgendwas.xy6, Imähl@irgendwas.xy21, Imähl@irgendwas.xy27
7WagnerImähl@irgendwas.xy7  WagnerImähl@irgendwas.xy7, Imähl@irgendwas.xy28
8BeckerImähl@irgendwas.xy8  BeckerImähl@irgendwas.xy8
9SchulzImähl@irgendwas.xy9  SchulzImähl@irgendwas.xy9
10HoffmannImähl@irgendwas.xy10  HoffmannImähl@irgendwas.xy10
11SchäferImähl@irgendwas.xy11  SchäferImähl@irgendwas.xy11
12MüllerImähl@irgendwas.xy12  MayerImähl@irgendwas.xy29
13SchmidtImähl@irgendwas.xy13  HerrmannImähl@irgendwas.xy30
14SchneiderImähl@irgendwas.xy14  KöhlerImähl@irgendwas.xy31
15FischerImähl@irgendwas.xy15  WalterImähl@irgendwas.xy32
16MüllerImähl@irgendwas.xy16  KönigImähl@irgendwas.xy33
17SchmidtImähl@irgendwas.xy17  SchulzeImähl@irgendwas.xy34
18SchneiderImähl@irgendwas.xy18  FuchsImähl@irgendwas.xy35
19FischerImähl@irgendwas.xy19  #BEZUG!#WERT!
20WeberImähl@irgendwas.xy20  #BEZUG!#WERT!
21MeyerImähl@irgendwas.xy21  #BEZUG!#WERT!
22MüllerImähl@irgendwas.xy22  #BEZUG!#WERT!
23SchmidtImähl@irgendwas.xy23  #BEZUG!#WERT!
24SchneiderImähl@irgendwas.xy24  #BEZUG!#WERT!
25FischerImähl@irgendwas.xy25    
26WeberImähl@irgendwas.xy26    
27MeyerImähl@irgendwas.xy27    
28WagnerImähl@irgendwas.xy28    
29MayerImähl@irgendwas.xy29    
30HerrmannImähl@irgendwas.xy30    
31KöhlerImähl@irgendwas.xy31    
32WalterImähl@irgendwas.xy32    
33KönigImähl@irgendwas.xy33    
34SchulzeImähl@irgendwas.xy34    
35FuchsImähl@irgendwas.xy35    
36      

Formeln der Tabelle
ZelleFormel
E1=INDEX(Liste_Unikate($A$1:$A$1000); ZEILE(A1))
F1=verketten2(E1;$A$1:$A$1000;$B$1:$B$1000)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
ransi

Anzeige
AW: nur mit VBA oder reichlich Hilfsspalten
11.08.2014 18:57:17
Timmie
Super, vielen Dank hierfür (und auch alle anderen Antwortenden), das sieht schon sehr nach dem aus, was ich gesucht habe.
hierbei noch eine Frage: Die Funktion "Verketten2" ist mir total unbekannt, kenne nur das normale "verketten". Ich hatte eben versucht die englisch übersetzung dafür zu googeln und bin komplett gescheitert, finde nur übersetzungen wo nur "verketten" auftaucht. Kennst Du die englische Funktion? Ich hoffe, mein Excel ist nicht veraltet.
Danke & VG

Anzeige
AW: nur mit VBA oder reichlich Hilfsspalten
11.08.2014 19:09:45
Daniel
Hi
Verketten2 ist keine originäre Excelfunktion.
Hier hat jemand in VBA eine solche Funktion geschrieben so gestaltet, dass man diese wie eine normale Excelfunktion verwenden kann.
Deswegen schreibt ransi ja auch "nur mit VBA oder vielen Hilfsspalten"
Den Code für Verketten2 mit zu posten hat er jetzt leider vergessen.
Wie es ohne VBA und mit nur einer Hilfsspalten mit einer einfachen Formel funktionieren kann, wenn die Daten sortiert sind, wurde dir hier ja schon gezeigt.
Gruß Daniel

Anzeige
AW: nur mit VBA oder reichlich Hilfsspalten
11.08.2014 19:20:33
Timmie
OK, vielen Dank.

'Verketten2' hat Ransi ja auch gar nicht ...
11.08.2014 19:35:13
Luc:-?
…erwähnt, Daniel,
und Timmie scheint ja die Alternative MxJoin, trotz Link, gar nicht zu interessieren…
Es ist doch immer wieder eine große Unkenntnis bzgl der Einsatz­möglichkeiten und -weise von UDFs feststellbar — nicht nur bei Level „VBA nein“!
Luc :-?

Anzeige
AW: 'Verketten2' hat Ransi ja auch gar nicht ...
11.08.2014 20:01:13
Daniel
Sorry Luc,
Ransi hat Verketten2 in der Spalte F seiner Beispieltabelle verwendet.
In der Jeanie-Darstellung werden die Formeltexte in der zweiten kleineren Tabelle unterhalb der grossen angezeigt.
Hast du die vielleicht übersehen?
Gruß Daniel

Ja, hab ich, weil er nichts dazu geschrieben ...
11.08.2014 20:48:25
Luc:-?
…hatte, Daniel,
und posten muss er den Code auch nicht, denn im Ggsatz zu MxJoin wimmelt das Archiv nur so von Verketten2 diverser Autoren. Die älteste Version könnte die von Sepp E. (2005/02+03) sein (VerkettenM dann 2010/06), eine von NoNet (2010/06), Jackd (2013/05), ANdreas (2005/09) usw. Auch ein VerkettenB kommt vor und wer weiß, was nicht noch alles. Ich habe auch mehrere UDFs und Subprozz geschrieben, die so etwas, zT als NebenEffekt, machen, von denen die Eine oder Andere auch im Archiv zu finden sein könnte.
Luc :-?

Anzeige
AW: Ja, hab ich, weil er nichts dazu geschrieben ...
11.08.2014 21:24:53
Daniel
Dann lies zukünftig die Beiträge besser, damit du nicht wieder falsche Behauptungen in die Welt setzt.
Sollte dir das trotzdem mal passieren, wäre eine kleine Entschuldigung angebracht, findest du nicht auch?

AW: Ja, hab ich, weil er nichts dazu geschrieben ...
12.08.2014 01:08:36
Ewald
Hallo,
reicht das vielleicht
 ABCDEF
1MüllerAdresse1 Adresse1;Adresse4;Adresse7  
2MeierAdresse2 Adresse2;Adresse5;Adresse8  
3ScmidtAdresse3 Adresse3;Adresse6;Adresse9  
4MüllerAdresse4 Adresse1;Adresse4;Adresse7  
5MeierAdresse5 Adresse2;Adresse5;Adresse8  
6ScmidtAdresse6 Adresse3;Adresse6;Adresse9  
7MüllerAdresse7 Adresse1;Adresse4;Adresse7  
8MeierAdresse8 Adresse2;Adresse5;Adresse8  
9ScmidtAdresse9 Adresse3;Adresse6;Adresse9  

ZelleFormel
D1=SVERWEIS2(A1;$A$1:$B$9;1;2;0;";")
D2=SVERWEIS2(A2;$A$1:$B$9;1;2;0;";")
D3=SVERWEIS2(A3;$A$1:$B$9;1;2;0;";")
D4=SVERWEIS2(A4;$A$1:$B$9;1;2;0;";")
D5=SVERWEIS2(A5;$A$1:$B$9;1;2;0;";")
D6=SVERWEIS2(A6;$A$1:$B$9;1;2;0;";")
D7=SVERWEIS2(A7;$A$1:$B$9;1;2;0;";")
D8=SVERWEIS2(A8;$A$1:$B$9;1;2;0;";")
D9=SVERWEIS2(A9;$A$1:$B$9;1;2;0;";")
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Funktion (auch von Ransi)
Public Function SVERWEIS2(Kriterium As String, _
Bereich As Range, _
SuchSpalte As Integer, _
ErgebnissSpalte As Integer, _
Optional Unikate As Boolean = True, _
Optional Trenner As String = ", ") As String
Dim arrTmp
Dim l As Long
Dim Mydic As Object
arrTmp = Bereich
Set Mydic = CreateObject("Scripting.Dictionary")
If Unikate = True Then
For l = 1 To UBound(arrTmp)
If arrTmp(l, SuchSpalte) = Kriterium Then Mydic(arrTmp(l, ErgebnissSpalte)) = 0
Next
SVERWEIS2 = Join(Mydic.keys, Trenner)
Else:
For l = 1 To UBound(arrTmp)
If arrTmp(l, SuchSpalte) = Kriterium Then Mydic(l) = arrTmp(l, ErgebnissSpalte)
Next
SVERWEIS2 = Join(Mydic.Items, Trenner)
End If
End Function
Gruß Ewald

Anzeige
Entschuldigung, falsche Behauptung? Na, ...
12.08.2014 03:23:59
Luc:-?
…du scheinst mir ja auf einem sehr hohen Ross zu sitzen, fall bloß nicht runter…
Die Rolle des „Wadenbeißers“ scheint dir ja schon mal zu liegen… :->>
Wann hast du dich übrigens mal entschuldigt…?
Und meine „Behauptung“ war per sé nicht falsch, denn Ransi hat eine dieser Fktt zwar verwendet (ggf in Visualisierung meiner Bemerkung), aber das tatsächlich nicht erwähnt, sondern nur allgemein VBA. Eine andere Sichtweise grenzt ja wohl an „Krümel­k…ei“!
Luc :-?

Anzeige
AW: Entschuldigung, falsche Behauptung? Na, ...
12.08.2014 09:00:52
Daniel
Hi luc
naja, wer hier der Wadenbeisser ist, ergibt sich ja aus der Reihenfolge der Beiträge.
Wenn Ransi schreibt, das er in der Zelle F1 die Formel "=Verketten2" verwendet, dann hat er Verketten2 erwähnt.
Das ist keine Kekskrümelei, sondern einfach eine Tatsache und dann kannst du nicht einfach das Gegenteil behaupten.
Gruß Daniel
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Excel Verketten über gesamte Spalte: So geht's

Schritt-für-Schritt-Anleitung

  1. Daten vorbereiten: Stelle sicher, dass Deine Daten in zwei Spalten angeordnet sind, zum Beispiel in Spalte A (Namen) und Spalte B (E-Mail-Adressen).

  2. Hilfsspalte einfügen: Füge eine Hilfsspalte (z.B. Spalte C) ein, um die verketteten E-Mail-Adressen zu speichern.

  3. Formel eingeben: Verwende die folgende Formel in Zelle C2:

    =WENN(A2=A1;C1&";"&B2;B2)

    Diese Formel überprüft, ob der Name in der aktuellen Zeile mit dem vorherigen übereinstimmt. Wenn ja, wird die E-Mail-Adresse an die vorherige E-Mail-Adresse angehängt.

  4. Formel nach unten ziehen: Ziehe die Formel nach unten, um sie auf die gesamte Spalte anzuwenden. Dies ermöglicht es Dir, die E-Mail-Adressen zu verketten, solange der Name gleich bleibt.

  5. Einzigartige Namen extrahieren: Um eine Liste der einzigartigen Namen zu erstellen, kannst Du die Funktion UNIKAT in Excel verwenden (verfügbar in neueren Versionen).

Häufige Fehler und Lösungen

  • Fehler: #BEZUG! oder #WERT!: Dies passiert oft, wenn die Formel auf eine leere Zelle verweist. Überprüfe die Daten in den angrenzenden Zellen und stelle sicher, dass alle notwendigen Daten vorhanden sind.

  • Falsche Verkettung von E-Mail-Adressen: Wenn die E-Mail-Adressen nicht richtig verkettet werden, stelle sicher, dass Du die Formel in der Hilfsspalte korrekt eingegeben hast und dass die Zellen nach unten gezogen wurden.

Alternative Methoden

  • Verwendung von VBA: Wenn Du eine dynamischere Lösung benötigst, kannst Du die Concat VBA-Funktion verwenden. Hier ein einfaches Beispiel:

    Function ConcatIf(range As Range, criteria As String, concatRange As Range, Optional delimiter As String = ", ") As String
   Dim cell As Range
   Dim result As String
   For Each cell In range
       If cell.Value = criteria Then
           result = result & cell.Offset(0, concatRange.Column - range.Column).Value & delimiter
       End If
   Next cell
   If Len(result) > 0 Then
       result = Left(result, Len(result) - Len(delimiter))
   End If
   ConcatIf = result
End Function

    Diese Funktion verbindet alle Werte, die einem bestimmten Kriterium entsprechen.

  • Verwendung von Power Query: In neueren Excel-Versionen kannst Du auch Power Query verwenden, um Daten zu transformieren und E-Mail-Adressen zu verketten.

Praktische Beispiele

Angenommen, Du hast folgende Daten:

A B
Müller mueller@example.com
Müller mueller2@example.com
Meier meier@example.com
Meier meier2@example.com

Die Formel in Spalte C würde wie folgt aussehen:

A B C
Müller mueller@example.com mueller@example.com
Müller mueller2@example.com mueller@example.com; mueller2@example.com
Meier meier@example.com meier@example.com
Meier meier2@example.com meier@example.com; meier2@example.com

Tipps für Profis

  • Verwendung von concat excel: In neueren Excel-Versionen kannst Du auch die TEXTVERKETTEN-Funktion verwenden, um E-Mail-Adressen in einem Schritt zu verbinden. Diese Funktion erlaubt es Dir, einen Trennzeichen anzugeben und ignoriert leere Zellen.

  • Nutze Named Ranges: Wenn Du oft mit den gleichen Daten arbeitest, kannst Du benannte Bereiche verwenden, um Deine Formeln übersichtlicher und leichter wartbar zu machen.

FAQ: Häufige Fragen

1. Wie funktioniert die CONCATENATE-Funktion in Excel?
Die CONCATENATE-Funktion (oder CONCAT in neueren Versionen) verbindet mehrere Textteile in einer Zelle. Beispiel: =CONCATENATE(A1, " ", B1) verbindet die Werte in A1 und B1 mit einem Leerzeichen dazwischen.

2. Gibt es eine Möglichkeit, die Formel auf die gesamte Spalte anzuwenden, ohne zu ziehen?
Ja, Du kannst die Formel in die gesamte Spalte eingeben, indem Du die gesamte Spalte auswählst und die Formel eingibst. Achte darauf, dass Du die Formel als Array-Formel eingibst, indem Du Strg + Shift + Enter drückst.

3. Welche Excel-Version benötige ich für die neuen Funktionen?
Die neuen Funktionen wie TEXTVERKETTEN sind in Excel 365 und Excel 2019 verfügbar. Ältere Versionen unterstützen diese Funktionen möglicherweise nicht.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige