Texte verbinden - Die Verknüpfung einzelner Textelemente zu einer Zeichenkette
Dafür gibt es die Funktion VERKETTEN, aber viel einfacher ist die Nutzung des kaufmännischen &-Zeichens. Jedes &-Zeichen verbindet Textelemente miteinander; als Argumente dürfen Zeichenfolgen (Texte), Zahlen und Bezüge auf einzelne Zellen verwendet werden.
Die Anzahl der verknüpften Textelemente ist auf max. 30 beschränkt. Diese Limitation können Sie aber aushebeln, indem Sie mehrere bereits verkettete Zellen nochmals verketten.
Hier einige Beispiele:
Zum Blattanfang
Einfaches Verbinden mit dem kaufmännischen &-Zeichen
|
|
| A | B | C | D | E | 1 | | | | | | 2 | | Haus | boot | Hausboot | | 3 | | Gummi | ente | Gummiente | | 4 | | | | | | |
|
Zum Blattanfang
Verbinden von Zellen mit Text - Einsatz des kaufmännischen &-Zeichens plus Text in Anführungszeichen:
|
|
| A | B | C | D | E | 1 | | | | | | 2 | | Norbert | Köln | Norbert aus Köln | | 3 | | Rosi | Hamburg | Rosi aus Hamburg | | 4 | | Norbert aus Köln und Rosi aus Hamburg. | |
---|
5 | | | | | | Formeln der Tabelle |
|
D2 : =B2&" aus "&C2 D3 : =B3&" aus "&C3 B4 : =D2&" und "&D3&"."
|
|
|
|
Zum Blattanfang
Möglichkeiten, das aktuelle Datum mit Text zu verbinden (z.B. in Vorlagen oder Formularen):
|
|
| A | B | C | D | E | 1 | | | | | | 2 | | | | Heute ist: Sonntag, 31.Okt.2004 | | 3 | | | | Köln, den 31. Oktober 2004 | | 4 | | Köln | 31.10.2004 | Köln, den 31. Oktober 2004 | | 5 | | 458954 | 31.10.2004 | Rechnung-Nr.: 458954 vom 31. Oktober 2004 | | 6 | | | | | | Formeln der Tabelle |
|
D2 : ="Heute ist: "&TEXT(HEUTE();"TTTT, TT.MMM.JJJJ") D3 : ="Köln, den "&TEXT(HEUTE();"TT. MMMM JJJJ") C4 : =HEUTE() D4 : =B4&", den "&TEXT(C4;"TT. MMMM JJJJ") C5 : =HEUTE() D5 : ="Rechnung-Nr.: "&B5&" vom "&TEXT(C4;"TT. MMMM JJJJ")
|
|
|
|
Zum Blattanfang
Möglichkeiten, Werte mit Maßeinheit mit Text zu verbinden:
|
|
| A | B | C | D | 1 | | | | | 2 | | 58 | Susi wiegt 58 kg | | 3 | | 500 | Über 500 m gewann er Silber. | | 4 | | | | | Formeln der Tabelle |
|
C2 : ="Susi wiegt "&B2&" kg" C3 : ="Über "&B3&" m gewann er Silber."
|
|
|
|
Zum Blattanfang
Einfaches Verbinden mit dem kaufmännischen &-Zeichen und Leerzeichen zwischen den Zellinhalten.
Beachten Sie "" für Erzeugung eines Leerzeichens!
|
|
| A | B | C | D | E | 1 | | | | | | 2 | | 50389 | Wesseling | 50389 Wesseling | | 3 | | 50321 | Brühl | 50321 Brühl | | 4 | | | | | | Formeln der Tabelle |
|
D2 : =B2&" "&C2 D3 : =B3&" "&C3
|
|
|
|
Zum Blattanfang
Verbinden mit führenden Nullen
Verbinden mit führenden Nullen, beachten Sie: z.B. TEXT(B3;"0000")
|
|
| A | B | C | D | 1 | | | | | 2 | | 0045 | 004578540005 | | 3 | | 7854 | | | 4 | | 0005 | | | 5 | | | | | Formeln der Tabelle |
|
C2 : =TEXT(B2;"0000")&TEXT(B3;"0000")&TEXT(B4;"0000")
|
|
|
|
Zum Blattanfang
Verbinden von Zellen mit Datumsformat
Verbinden von Zellen mit Datumsformat z.B. für Zelle C3: TEXT(B4;"TT.MM.JJJJ")
|
|
| A | B | C | D | 1 | | | | | 2 | | Am | Am 14.04.2004 entlief ein Tiger. | | 3 | | 14.04.2004 | | | 4 | | entlief | | | 5 | | ein | | | 6 | | Tiger. | | | Formeln der Tabelle |
|
C2 : =B2&" "&TEXT(B3;"TT.MM.JJJJ")&" "&B4&" "&B5&" "&B6
|
|
|
|
Zum Blattanfang
Verbinden mit Zeilenumbruch
Beim Verbinden einen Zeilenumbruch einbauen, hier in Kombination mit Datumsformat: Der "weiche Zeilenumbruch" (Besonderheit: er schließt die Zelle nicht ab) wird erzeugt mit: ZEICHEN(10). Die Zelle muss dann mit Zeilenumbruch formatiert sein.
|
|
| A | B | C | D | E | F | 1 | | | | | | | 2 | | Otto | Legerlotz | 10.01.1956 | Otto Legerlotz 10.01.1956 | | 3 | | | | | | | Formeln der Tabelle |
|
E2 : =B2 & " " & C2 & ZEICHEN(10)& TEXT(D2;"TT.MM.JJJJ")
|
|
|
|
Zum Blattanfang
Verbinden nutzen für Serienbriefe
Für Serienbriefe ist eine solche Anredekombination recht praktisch, Personen über 18 Jahren werden je nach Geschlecht mit "Sehr geehrte Frau x," / "Sehr geehrter Herr y," ansgesprochen. Personen unter 18 Jahren mit "Liebe x," / "Lieber y,".
Die Formel in Spalte F berechnet die Zeitdifferenz in Jahren zwischen Spalte D (geb.) und HEUTE() und berücksichtigt als weiteres Kriterium das Geschlecht in Spalte E:
| A | B | C | D | E | F | G |
1 | | | | | | | |
2 | | Name | Vorname | geb. | Geschlecht | Anrede | |
3 | | Schmitz | Bernd | 25.08.1972 | m | Sehr geehrter Herr Schmitz, | |
4 | | Müller | Marie | 16.10.1990 | w | Liebe Marie, | |
5 | | Decker | Joachim | 05.01.1995 | m | Lieber Joachim, | |
6 | | Zons | Hilde | 03.04.1965 | w | Sehr geehrte Frau Zons, | |
7 | | | | | | | |
Formeln der Tabelle |
Zelle | Formel | F3 | =WENN(UND(DATEDIF(D3;HEUTE();"y")<18;E3="m");"Lieber "&C3&",";WENN(UND(DATEDIF(D3;HEUTE();"y")<18;E3="w");"Liebe "&C3&",";WENN(E3="m";"Sehr geehrter Herr "&B3&",";"Sehr geehrte Frau "&B3&","))) |
|
Die Formel aus F3 kann runterkopiert werden.
Zum Blattanfang
Verbinden von Zellen mit Trennzeichen auch bei Leerzellen im Bereich
Sie möchten Zellinhalte mehrer Zellen verbinden und ein Trennzeichen zwischen die einzelnen Zellinhalte einfügen. Wenn sich im Bereich aber Leerzellen befinden, folgt daraus bei der üblichen Verbinden-Methode, dass zwei Trennzeichen aufeinander folgen. Der verstorbene MVP Frank Kabel hat eine Formel entwickelt, die das verhindert. Es können auch mehrere Leerzellen in Folge verbunden werden. Die Formel funktioniert, insofern in den einzelnen Zellen keine Leerzeichen vorkommen:
| A | B | C | D | E | F |
1 | Tomaten | Bohnen | Erbsen | Möhren | | Tomaten / Bohnen / Erbsen / Möhren |
2 | Tomaten | | | Möhren | | Tomaten / Möhren |
3 | | Bohnen | | Möhren | | Bohnen / Möhren |
4 | | | Erbsen | | | Erbsen |
Formeln der Tabelle |
Zelle | Formel | F1 | =WECHSELN(GLÄTTEN(A1&" "&B1&" "&C1&" "&D1);" ";" / ") |
|
Die Formel aus F1 kann nach unten kopiert werden und ist natürlich erweiterbar.
Zum Blattanfang
Wörter aus vielen Zellen per Hilfsspalte über Kurzformel verbinden
| A | B | C | D |
1 | | | | |
2 | | Einzelwort | Verkettung | |
3 | | Heute | Heute gehe ich Schlittschuh laufen, wenn das Eis hält. | |
4 | | gehe | gehe ich Schlittschuh laufen, wenn das Eis hält. | |
5 | | ich | ich Schlittschuh laufen, wenn das Eis hält. | |
6 | | Schlittschuh | Schlittschuh laufen, wenn das Eis hält. | |
7 | | laufen, | laufen, wenn das Eis hält. | |
8 | | wenn | wenn das Eis hält. | |
9 | | das | das Eis hält. | |
10 | | Eis | Eis hält. | |
11 | | hält. | hält. | |
12 | | | | |
Die Formel aus Zelle C3 kann runterkopiert werden.
Zum Blattanfang
Erläuterung zur Funktion VERKETTEN
In Foren oder der Excel-Hilfe findet man Hinweise zu dem Thema häufig unter dem Stichwort "VERKETTEN". Jedes der obigen Beispiele kann auch durch die Funktion VERKETTEN erzeugt werden, aber sie verlängert die Formeln und macht sie schnell unübersichtlich und das bei gleichem Ergebnis:
| A | B | C | D | E |
1 | | | | | |
2 | | 50389 | Wesseling | 50389 Wesseling | |
3 | | 50321 | Brühl | 50321 Brühl | |
4 | | | | | |
Formeln der Tabelle |
Zelle | Formel | D2 | =VERKETTEN(B2;" ";C2) |
|
Die Formel aus D2 kann runterkopiert werden.