20.04.2018, 09:09 (Dieser Beitrag wurde zuletzt bearbeitet: 20.04.2018, 09:34 von Luffy.)
Hallo zusammen,
ich benötige eine Formel für folgendes scenario:
In Spalte C stehen mehrzeilig Textzeilen (mal mehr mal weniger Zeilen) In Spalte B steht eine Positionsnummer, diese Nummer markiert den Start des Textes der zusammengefügt werden soll.
Beispiel:
Spalte B | Spalte C
P12345 | Korpus | 3000x900x1000mm | ,Ausführung: MDF wasserfest P54321 | Tisch | 2000x1000x800mm | ,Ausführung: Gestell in Metall, Tischplatte in Massivholz
In Spalte E soll dann neben der Positionsnummer der gesamte Text stehen.
Ergebnis:
Spalte B | Spalte C | Spalte E P12345 | Korpus | Korpus 3000x900x1000mm, Ausführung: MDF wasserfest | 3000x900x1000mm, | Ausführung: MDF wasserfest P54321 | Tisch | Tisch 2000x1000x800mm, Ausführung: Gestell in Metall, Tischplatte in Holz | 2000x1000x800mm | ,Ausführung: Gestell in Metall, Tischplatte in Holz
Unteranderem sollte ein " " (Leerzeichen) beim zusammenfügen pro Zeile eingefügt werden.
Ich mache das mühsam mit =C1&" "&C2&" "&C3....
Hoffe jemand hat eine Lösung für mich!
EDIT: Die Positionsnummern beginnen nicht immer mit "P...." also sobald in Spalte B irgentwas steht soll der Text nach unten zusammengefügt werden bis die nächste Nummer beginnt oder ein Zeichen erscheint
da Excel 2013 die Funktion Textverketten noch nicht kennt, empfehle ich eine UDF (User Defined Function).
Diese Funktion muss in ein allgemeines Modul des VBA-Projektes der Datei eingetragen werden.
Code:
Option Explicit
Public Function Verbinden(rngListe As Range) As String
Dim lngZeile As Long
Dim lngZeilen As Long
Dim blnWeiter As Boolean
Dim varListe As Variant
blnWeiter = True
Verbinden = ""
If rngListe.Columns.Count = 1 Then
Verbinden = "#Fehler, mindestens zwei Spalten"
Else
varListe = rngListe.Value
If varListe(1, 1) <> "" Then
Verbinden = varListe(1, 2)
lngZeile = 2
lngZeilen = UBound(varListe, 1)
Do While lngZeile <= lngZeilen And blnWeiter
If varListe(lngZeile, 1) = "" Then
Verbinden = Verbinden & " " & varListe(lngZeile, 2)
Else
blnWeiter = False
End If
lngZeile = lngZeile + 1
Loop
End If
End If
End Function
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
der Anfang war doch schon mal richtig. Du kannst das mit einer Formel, die du runterziehst, einfach bewerkstelligen; mühsam alles einzeln einzugeben ist nicht nötig.
Arbeitsblatt mit dem Namen 'Tabelle1'
A
B
C
1
P-12345
korpus
korpus platte beine
2
platte
3
beine
4
99999-a
holz
holz kunststoff glas
5
kunststoff
6
glas
7
aaa
alu
alu chrom stahl
8
chrom
9
stahl
Zelle
Formel
C1
=WENN(A1<>"";B1&" "&B2&" "&B3;"")
Verwendete Systemkomponenten: [Windows (32-bit) NT :.00] MS Excel 2013
Achtung! Matrixformel. Die geschweiften Klammern werden nicht mit eingegeben, sondern jede Bearbeitung wird mit Strg+Shift+Enter abgeschlossen.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
(20.04.2018, 12:29)Ego schrieb: für alle die Textverketten nutzen können
Moin!
Hier im Forum können ALLE auf Textverketten() zugreifen, da sie über einen Internetanschluss verfügen.
(Stichwort: onedrive.live.com und dann Neu, Excel-Arbeitsmappe)
Als reine Alternative habe ich mal etwas ohne Matrixformel entwickelt.
Dazu müssen aber die Lücken in Spalte A zunächst mit dem oberen Wert aufgefüllt werden:
A1 markieren, Strg+a
F5, Inhalte, Leerzellen
in die aktivierte Zelle A3: =A2 mit Strg+Enter abschließen
Markierung belassen
Strg+1, ben.def. Zahlenformat ;;; vergeben, damit der Inhalt unsichtbar wird
Jetzt reicht die Formel: =WENN(A2<>A1;TEXTVERKETTEN(" ";WAHR;BEREICH.VERSCHIEBEN(B2:B1000;0;0;ZÄHLENWENN(A:A;A2)));"")
21.04.2018, 09:24 (Dieser Beitrag wurde zuletzt bearbeitet: 21.04.2018, 09:31 von RPP63.)
… und wer das volatile BEREICH.VERSCHIEBEN() nicht mag, kann natürlich auch INDEX() nehmen: =WENN(A2<>A1;TEXTVERKETTEN(" ";WAHR;INDEX(B2:INDEX(B2:B9;ZÄHLENWENN(A:A;A2));;1));"")
21.04.2018, 10:23 (Dieser Beitrag wurde zuletzt bearbeitet: 21.04.2018, 10:23 von LCohen.)
Excel-Online kann TEXTVERKETTEN(), wie auch xl365, GoogleDocs (dort einmal eine Einschränkung erlebt) und LO ab 6 (dort heißt es VERBINDEN()).
Oft wird es aber als {}-Formel benötigt. Dann folgendes Vorgehen:
In lokalem Excel ohne TEXTVERKETTEN() =_xlfn.TEXTJOIN() 1)verwenden und blind {}en.Blind heißt: Man sieht mangels TEXTVERKETTEN kein Ergebnis.
In OneDrive speichern mit Excel-Online öffnen.
{}-Formeln werden dort normal gerechnet, mit {} angezeigt und können kopiert und gelöscht werden. Auch Bezüge können darin verschoben werden, wenn man die Quellen verschiebt und die Formel selbst nicht anfasst. Nur editiert kann {} nicht werden.
LO und Docs kosten nichts und können die Eingabe daher komfortabler machen, als mit dem Blindsprung.
1) das geht ab xl2007 und mit Compatibility Pack ab xl2000.
