Zellen in einem Bereich zu einem Text verbinden

Kai_8e2bec · 11. November 2019 um 12:13

Hallo!

Mit welcher Funktion kann man sowas wie =A1&" "&B1 auf einen beliebigen Bereich anwenden (nicht festgelegter Länge)

-> XXXXX(A1:B1) oder XXXXX(C2:X2)

(ich meine nicht VERKETTEN(), weil da nur einzelne Zellen als Argument angegeben werden können)

DANKE cu kai

Thomas_Ramel · 11. November 2019 um 12:13

Grüezi Kai

Mit welcher Funktion kann man sowas wie =A1&" "&B1 auf einen
beliebigen Bereich anwenden (nicht festgelegter Länge)

-> XXXXX(A1:B1) oder XXXXX(C2:X2)

(ich meine nicht VERKETTEN(), weil da nur einzelne
Zellen als Argument angegeben werden können)

Verwende dazu eine Benutzerdefinierte Funktion - den folgenden Code in en Modul deiner Mappe kopieren, dann steht sie dir zur Verfügung:

Public Function VerknuepfenSpez(vRng As Variant, \_
 Optional sDelim As String = "", \_
 Optional sEnd As String = "") \_
 As String

'© [email protected] / 02.03.2006
'Verknüpft die Inhalte eines Zellbereiches mit eigenen Trennzeichen.
'Leere Zellen werden nicht berücksichtigt
'Beispiele: =VerknüpfenSpez(A1:smiley:1;",";".") =VerknüpfenSpez(A1:smiley:1)

Dim a() As String
Dim lngI As Long
Dim rCell As Range

 ReDim a(Application.WorksheetFunction.CountA(vRng))

 For Each rCell In vRng
 If Not IsEmpty(rCell) Then
 a(lngI) = rCell.Text
 lngI = lngI + 1
 End If
 Next rCell
 ReDim Preserve a(lngI - 1)
 VerknuepfenSpez = Join(a(), sDelim) & sEnd
End Function

–
Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Kai_8e2bec · 11. November 2019 um 12:13

Hallo! Danke für die Info!

Wollte eigentlich ohne VBA auskommen (Excel 2007 bzw. unsere Umgebung macht sonst jedesmal nen Sicherheitsaufstand…)

-> kann man VBA-Funktionen wie JOIN() nicht direkt in Excel nutzen?

Danke cu kai

Reinhard_fb8cb7 · 11. November 2019 um 12:13

Hallo Kai,

Wollte eigentlich ohne VBA auskommen (Excel 2007 bzw. unsere
Umgebung macht sonst jedesmal nen Sicherheitsaufstand…)

*hmmh* deine Sache den Admin zu becircen oder zu verprügeln bis er Makros zuläßt

-> kann man VBA-Funktionen wie JOIN() nicht direkt in Excel
nutzen?

?

Beschreib mal bitte viel genauer was du da vorhast.

Gruß
Reinhard

Kai_8e2bec · 11. November 2019 um 12:13

Hallo!

jaaa eichendlich was gaaanz harmloses…

Liste ausm Web eingelesen -> alles in einer Zelle pro Zeile -> grr…

Ganz Schlau: Alt+N+T = Daten aufteilen, geht nur mit Leerzeichen
-> die ganzen Bezeichungen auch aufgedröselt
-> unterschiedlich viel Spalten pro Zeile -> wieder grrr…

mit ANZAHL2() in Spalte W die Spalten davor gezählt und z. B. folgendes gedacht:
-> in Zelle X2 =A2
-> in Zelle Y2 =GLÄTTEN(JOIN(BEREICH.VERSCHIEBEN(A2;0;1;1;W2-2)))
-> in Zelle Z2 =BEREICH.VERSCHIEBEN(A2;0;W2-1)

wenn es JOIN oder sowas gäbe…

aber gut geht auch anders - sieht nur nicht so elegant aus,
wenn man halt alles mit B2&" "&C2… zusammenpappt;
mit GLÄTTEN() bekommt man die zuvielen " " auch wieder weg…

Danke trotzdem für Deine Mithilfe!

cu kai

Reinhard_fb8cb7 · 11. November 2019 um 12:13

Hallo Kai,

jaaa eichendlich was gaaanz harmloses…

jaja, sagen alle, dahinter steckt dann ein 2 Minuten-Job, ein 5-Tage-Job oder was unlösbares

Liste ausm Web eingelesen -> alles in einer Zelle pro Zeile ->
grr…

Normal.

Ganz Schlau: Alt+N+T = Daten aufteilen, geht nur mit
Leerzeichen

Das ist doch Text in Spalten, Leerzeichen ist eine Möglichkeit der Trennung.

-> die ganzen Bezeichungen auch aufgedröselt
-> unterschiedlich viel Spalten pro Zeile -> wieder grrr…

Naja, es wird strikt bei jedem Leerzeichen getrennt.

mit ANZAHL2() in Spalte W die Spalten davor gezählt und z. B.
folgendes gedacht:
-> in Zelle X2 =A2
-> in Zelle Y2
=GLÄTTEN(JOIN(BEREICH.VERSCHIEBEN(A2;0;1;1;W2-2)))
-> in Zelle Z2 =BEREICH.VERSCHIEBEN(A2;0;W2-1)

Uppsala, mir unklar was du da willst.

Kannst du mit rapidshare ( FAQ:2606 ) mal ne Mappe hochladen wo in Tab1 deine wie auch immer eingelesene Liste steht und in Tab2 wie diese Liste nach Aufteilung in Spalten aussehen sollte.

wenn es JOIN oder sowas gäbe…

Mir unklar warum da Join ins Spiel kommt.

Gruß
Reinhard

Thomas_Ramel · 11. November 2019 um 12:14

Grüezi Kai

Wollte eigentlich ohne VBA auskommen (Excel 2007 bzw. unsere
Umgebung macht sonst jedesmal nen Sicherheitsaufstand…)

-> kann man VBA-Funktionen wie JOIN() nicht direkt in Excel
nutzen?

Aber klar doch, das geht ganz einfach über eine Benutzerdefinierte Funktion - oder was denkst Du wieso diese Funktionen VBA-Funktionen genannt werden… )

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Kai_8e2bec · 11. November 2019 um 12:15

Hallo Reinhard!

Liste ausm Web eingelesen -> alles in einer Zelle pro Zeile -> grr…

Normal.

ja

=GLÄTTEN(JOIN(BEREICH.VERSCHIEBEN(A2;0;1;1;W2-2)))
-> in Zelle Z2 =BEREICH.VERSCHIEBEN(A2;0;W2-1)

Uppsala, mir unklar was du da willst.

kann ich auch hier machen (/ soll mal der Zellen markieren, ok?):

Textdatei (die eingelesen wird):
123 Dings vom Dach TYP1 33,33
345 bla bla TYP1 22,2
678 blubber TYP2 44
901 nörgel TYP1 17
234 nörgel TYP1 04
567 Dings vom Dach TYP1 0,01

Excel macht daraus (nach Text in Spalten - ja hast ja recht… ):
123/Dings/vom/Dach/TYP1/33,33 = 6 Spalten
345/bla/bla/TYP1/22,2 = 5 Spalten
678/blubber/TYP2/44 = 4 Spalten
901/nörgel/TYP1/17 = 4 Spalten
234/nörgel/TYP1/4 = 4 Spalten
567/Dings/vom/Dach/TYP1/0,01 = 6 Spalten

Aussehen soll es so (ggf. per Formel in den Spalten nach dem von grad eben):
123/Dings vom Dach/TYP1/33,33 = 4 Spalten
345/bla bla/TYP1/22,2 = 4 Spalten
678/blubber/TYP2/44 = 4 Spalten
901/nörgel/TYP1/17 = 4 Spalten
234/nörgel/TYP1/4 = 4 Spalten
567/Dings vom Dach/TYP1/0,01 = 4 Spalten

Mir unklar warum da Join ins Spiel kommt.

Join war die Funktion in dem Makrovorschlag…

aber (für den einen Fall zumindest) hab ich mir schon anders beholfen;
eine Funktion a la VERKETTEN, die mit Zellbereichen umgehen hätt ich schlicht eleganter gefunden - das war alles

Danke aber für Deine Mühen!! cu Kai

Reinhard_fb8cb7 · 11. November 2019 um 12:15

kann ich auch hier machen (/ soll mal der Zellen markieren,
ok?):

Textdatei (die eingelesen wird):
123 Dings vom Dach TYP1 33,33
345 bla bla TYP1 22,2
678 blubber TYP2 44
901 nörgel TYP1 17
234 nörgel TYP1 04
567 Dings vom Dach TYP1 0,01

Aussehen soll es so (ggf. per Formel in den Spalten nach dem
von grad eben):
123/Dings vom Dach/TYP1/33,33 = 4 Spalten
345/bla bla/TYP1/22,2 = 4 Spalten
678/blubber/TYP2/44 = 4 Spalten
901/nörgel/TYP1/17 = 4 Spalten
234/nörgel/TYP1/4 = 4 Spalten
567/Dings vom Dach/TYP1/0,01 = 4 Spalten

Hallo Kai,

vielleicht so:

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │ E │
──┼───────────────────────────────┼─────┼────────────────┼──────┼───────┤
1 │ 123 Dings vom Dach TYP1 33,33 │ 123 │ Dings vom Dach │ TYP1 │ 33,33 │
──┼───────────────────────────────┼─────┼────────────────┼──────┼───────┤
2 │ 345 bla bla TYP1 22,2 │ 345 │ bla bla │ TYP1 │ 22,2 │
──┼───────────────────────────────┼─────┼────────────────┼──────┼───────┤
3 │ 678 blubber TYP2 44 │ 678 │ blubber │ TYP2 │ 44 │
──┼───────────────────────────────┼─────┼────────────────┼──────┼───────┤
4 │ 901 nörgel TYP1 17 │ 901 │ nörgel │ TYP1 │ 17 │
──┼───────────────────────────────┼─────┼────────────────┼──────┼───────┤
5 │ 234 nörgel TYP1 04 │ 234 │ nörgel │ TYP1 │ 04 │
──┼───────────────────────────────┼─────┼────────────────┼──────┼───────┤
6 │ 567 Dings vom Dach TYP1 0,01 │ 567 │ Dings vom Dach │ TYP1 │ 0,01 │
──┴───────────────────────────────┴─────┴────────────────┴──────┴───────┘
Benutzte Formeln:
B1: =LINKS(A1;FINDEN(" ";A1)-1)
B2: =LINKS(A2;FINDEN(" ";A2)-1)
B3: =LINKS(A3;FINDEN(" ";A3)-1)
B4: =LINKS(A4;FINDEN(" ";A4)-1)
B5: =LINKS(A5;FINDEN(" ";A5)-1)
B6: =LINKS(A6;FINDEN(" ";A6)-1)
C1: =TEIL(A1;FINDEN(" ";A1)+1;FINDEN("TYP";A1)-6)
C2: =TEIL(A2;FINDEN(" ";A2)+1;FINDEN("TYP";A2)-6)
C3: =TEIL(A3;FINDEN(" ";A3)+1;FINDEN("TYP";A3)-6)
C4: =TEIL(A4;FINDEN(" ";A4)+1;FINDEN("TYP";A4)-6)
C5: =TEIL(A5;FINDEN(" ";A5)+1;FINDEN("TYP";A5)-6)
C6: =TEIL(A6;FINDEN(" ";A6)+1;FINDEN("TYP";A6)-6)
D1: =TEIL(A1;FINDEN("TYP";A1);4)
D2: =TEIL(A2;FINDEN("TYP";A2);4)
D3: =TEIL(A3;FINDEN("TYP";A3);4)
D4: =TEIL(A4;FINDEN("TYP";A4);4)
D5: =TEIL(A5;FINDEN("TYP";A5);4)
D6: =TEIL(A6;FINDEN("TYP";A6);4)
E1: =TEIL(A1;FINDEN("TYP";A1)+5;99)
E2: =TEIL(A2;FINDEN("TYP";A2)+5;99)
E3: =TEIL(A3;FINDEN("TYP";A3)+5;99)
E4: =TEIL(A4;FINDEN("TYP";A4)+5;99)
E5: =TEIL(A5;FINDEN("TYP";A5)+5;99)
E6: =TEIL(A6;FINDEN("TYP";A6)+5;99)

A1:E6
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363
Gruß
Reinhard

Kai_8e2bec · 11. November 2019 um 12:15

Hallo Reinhard!

ja klar so gehts auch, aber das muss dann immer individuell angepasst werden (was machst Du, wenn sich der Textteil je Zeile nicht nur aus 1-3 sondern aus 1-10 oder 1-20 Worten zusammensetzt und wenn die vorletzte Spalte nicht bei allen mit TYP anfängt sondern z. B. unterschiedliche Stadtnamen enthält…)

meine Lösung war dann eben (nach Text-in-Spalten), dass ich

die beiden hinteren Teile mit BEREICH.VERSCHIEBEN(…,ANZAHL2(…))
und BEREICH.VERSCHIEBEN(…,ANZAHL2(…)-1) aus der Liste „gezogen“ habe
Textteil mit &" "& wieder zusammengesetzt habe (alle Spalten ab B)
und dann die o.g. Spalten (Typ und Zahl) durch WECHSELN()rausgenommen habe…

Damit muss ich nicht 10-20x nach " " suchen (macht ja Text-in-Spalten für mich)…

Aber es führen ja viele Wege nach Rom
(meinen Weg hier nur skizziert, falls mal jemand auch sowas sucht/brauchen kann…)

cu kai