Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1612to1616
1612to1616
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Mit VBA Text in 3 Spalten aufteilen

Forumthread: Mit VBA Text in 3 Spalten aufteilen

Mit VBA Text in 3 Spalten aufteilen
08.03.2018 18:06:44
Albin
Hallo Zusammen
Mir fehlt noch das Pünktli auf dem i.
Ich möchte den Text z.B. "11 - Mitte01 / AB2" aufteilen in 3 neue Spalten. Die Quell ist in Spalte I abgelegt. Da jetzige Makro schreibt mir schön in der ersten Spalte F den Wert "12" rein. In der 3-Spalte H den Wert "AB1" rein.
Leider passt die mittlere Spalte G noch nicht, dort kommt der falsche Text mit "Mitte01 / AB2" daher. Es sollte aber nur "Mitte01" stehen.
Ich komme einfach mit der Abfrage für die Spalte G, ab Sonderzeichen - bis zum Sonderzeichen / nicht klar. :-(
Ich danke für Eure Hilfe.
LG
Albin
https://www.herber.de/bbs/user/120296.xlsm
Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Mit VBA Text in 3 Spalten aufteilen
08.03.2018 18:40:36
Daniel
Hi
probiers mal mit diesen Formeln:
F2: =Links($I2;Finden("-";$I2)-1)
G2: =Teil($I2;Finden("-";$I2)+1;Finden("/";$I2)-Finden("-";$I2)-1)
H2: =Teil($I2;Finden("/";$I2)+1;999)

wenn die Leerzeichen stören, hilft GLÄTTEN()
um Spalte A auch noch zu berücksichtigen, ergänzt du in den Formeln noch:
=Wenn($A2="";"";und hier dann die andere Formel)
per Makro gäbs aber auch noch ne andere elegante Variante:
1. Ersetze in Spalte I das "-" durch "/"
2. wende dann DATEN - DATENTOOLS - TEXT IN SPALTEN auf die Spalte I an, mit dem "/" als anderes Trennzeichen und der Zelle F2 als Ziel
falls I unverändert erhalten bleiben soll, einfach die Spalte nochmal kopieren, kannst du ja hinterher wieder löschen
Gruß Daniel
Anzeige
AW: Mit VBA Text in 3 Spalten aufteilen
08.03.2018 19:09:13
Albin
Hoi Daniel
Passt noch nicht so ganz. Den die Einträge in der Spalte I, importiere ich aus einer SQL - DB. Leider wäre der Aufwand sehr gross, dies zu ändern. Sorry
Was ich brauche ist die richtige Abfrage im Makro. siehe Upload oder Makro
https://www.herber.de/bbs/user/120296.xlsm 
Sub formal_V3()
Dim wks As Worksheet
Dim Zei_L As Long
Set wks = ActiveSheet
Application.Calculation = xlCalculationManual
With wks
Zei_L = .Cells(.Rows.Count, 1).End(xlUp).Row
If Zei_L >= 2 Then
With .Range(.Cells(2, 1), .Cells(Zei_L, 1))
If Application.WorksheetFunction.CountBlank(.Cells) = 0 Then
.Offset(0, 5).FormulaR1C1 = "=LEFT(RC[4],FIND(""-"",RC[4])-1)"
.Offset(0, 6).FormulaR1C1 = "=Reght(RC[3],FIND(""-"",RC[4])-1)"  '' test
.Offset(0, 7).FormulaR1C1 = "=RIGHT(RC[2],LEN(RC[2)-FIND(""-"",RC[1]))"
Else
With .SpecialCells(xlCellTypeConstants)
.Offset(0, 5).FormulaR1C1 = "=LEFT(RC[3],FIND(""-"",RC[3])-1)"
.Offset(0, 6).FormulaR1C1 = "=Right(RC[2],FIND(""/"",RC[2])-1)"  ''da muss es sein " _
.Offset(0, 7).FormulaR1C1 = "=RIGHT(RC[1],LEN(RC[1])-FIND(""/"",RC[1]))"
End With
End If
End With
End If
End With
Application.Calculation = xlCalculationAutomatic
End 


Sub
Gruss Albin
Anzeige
AW: Mit VBA Text in 3 Spalten aufteilen
08.03.2018 19:30:41
Daniel
HI
das passt schon.
wenn du einen Teiltext aus der Mitte eines Textes auslesen willst, brauchst du die Funktion TEIL und als zusätzlichen Parameter die Startposition (also das Zeichen ab dem gelesen wird), sowie die Länge des Teiltextes.
Diese ergibt sich aus der Postion des BIS-Zeichens minus der Position des AB-Zeichens.
da du die Trennzeichen nicht mitlesen willst, halt immer noch um 1 verschoben.
du musst doch nur in dieser Zeile: 
.Offset(0, 6).FormulaR1C1 = "=Right(RC[2],FIND(""/"",RC[2])-1)"

meine geänderte Formel für die Spalte G verwenden.
um dir die Formel in der englischen R1C1-Schreibweise anzeigen zu lassen, kannst du auch meine deutsche Formel von Hand in die Zelle eintragen und dann im Direktfenster den passenden Formeltext mit:
?Selection.FormulaR1C1
anzeigen lassen. von dort kannst du dann die Formel in den Code kopieren (doppeln der Anführungszeichen nicht vergessen!)
und noch ein kleiner Tip: die Formeln werden für die R1C1-Schreibweise kürzer und einfacher, wenn du so viele Zellbezüge wie möglich absolut setzt (hier alle Spaltenbezüge).
Gruß Daniel
Anzeige
AW: Mit VBA Text in 3 Spalten aufteilen
08.03.2018 20:21:08
Albin
Lieber Daniel
Ich bin kein Profi, wie Du! Einigermassen habe ich deine Hilfe verstanden. Laub mir, ich mache es mir nicht einfach und frage einfach im Forum. Mich nervst es einfach tödlich, wenn ich nach langem Versuche, es nicht hinbekomme. Könntest Du mir bitte das ganze Makro so anpassen und zusenden, dass es läuft.
Das wäre total lieb von Dir.
Albin
PS: Vielleicht bin ich mal in 10 Jahr soweit wie Du, wenn überhaupt?!
Anzeige
AW: Mit VBA Text in 3 Spalten aufteilen
08.03.2018 20:44:32
Daniel
naja, ich bin nicht dein no-cost-Dienstleister!
den Rest des Codes hast du ja auch irgendwie hinbekommen.
Es ist ist ja nur die andere Formel, die du da einsetzen musst.
Die Formel kannst du ja erst mal unabhängig vom Makro testen (Excel gut!!!)
Wenn die Formel dann funktioniert, kannst du ihren Text über den Recorder oder wie von mir gezeigt anzeigen lassen und in den Code übernehmen.
Gruß Daniel
Anzeige
AW: Mit VBA Text in 3 Spalten aufteilen
08.03.2018 21:28:22
Albin
naja, habe es schon versucht.
Danke für die Unterstützung, versuche es weiterhin allein.
Mit deiner zweit Formel, komme ich sicher weiter in der Lösungsuche.
Excel gut!!! was heisst heute zutage schon Gut? Zum Beispiel Cremeschnitten sind sicher vielmals GUT.
= Glätten(I2...) brachte nicht viel.
FG
Albin
Anzeige
AW: Mit VBA Text in 3 Spalten aufteilen
08.03.2018 21:39:37
Daniel
naja, hast du wenigstens mal meine Formel von Hand getestet, ob sie das richtige Ergebnis liefert?
einfach mal die Formel in die Zelle schreiben solltest du ja hinbekommen.
bzw das Glätten muss außen rum, für den Fall, dass du die Leerzeichen am Anfang und Ende weg haben willst:
also diese Formel in G2: =Glätten(Teil($I2;Finden("-";$I2)+1;Finden("/";$I2)-Finden("-";$I2)-1))
liefert die das passende Ergebnis in Spalte G?
Anzeige
Da du ohnehin VBA verwenden willst, ...
09.03.2018 00:34:19
Luc:-?
…Albin,
könntest du auch eine UDF (mit mehr Komfort) verwenden, zB so:
F2:H2: {=SplitVx(I2;{" - "." / "})}
Falls der 1.Wert auch als echte Zahl dargestellt wdn soll, dann so:
F2:H2: {=WENNFEHLER(--SplitVx(I2;{" - "." / "});SplitVx(I2;{" - "." / "}))}
Alternativ könnte auch eine andere UDF benutzt wdn, die diese Umwandlung bereits enthält:
F2:H2: {=VSplit(I2;{" - "." / "};1)}
Da ich bereits mit einer bisher unpublizierten Version dieser UDF arbeite, könnte es sein, dass die letzt­publizierte das 2.Argument nicht verträgt. Dann besser so:
F2:H2: {=VSplit(WECHSELN(I2;"-";"/");" / ";1)}
Als ZellFmln sind das natürlich (plurale) MatrixFmln über alle 3 Spalten einer Zeile. Allerdings kann man die UDFs auch in einer Sub­pro­zedur verwenden, wobei ein Array aus 3 Werten entsteht. Dabei können auch Werte bedingungsabhängig weg­ge­las­sen bzw in diesem aus­ge­tauscht wdn, wobei Letz­teres dann ggf an einer Array-Kopie erfolgen sollte. Mit ZellFmln wäre das zwar auch möglich, erforderte aber eine längere Fml.
Natürlich kann das in einer Subprozedur auch mit der vbFkt Split bewerkstelligt wdn; die ist aber weniger komfortabel, was dich ja hier­bei nicht unbedingt stören dürfte.
Die UDFs sind im Archiv enthalten und dort in etlichen Beiträgen auch verlinkt. Allerdings scheint die Archiv-Recherche hier schon seit Wochen (oder gar Monaten) nicht mehr zu fktionieren, weshalb du wohl herber-extern nach ihnen suchen oder bei Interesse nochmal nachfragen müsstest.
Gruß, Luc :-?
Anzeige
AW: Da du ohnehin VBA verwenden willst, ...
09.03.2018 09:17:16
snb 

Sub M_snb()
sn = Columns(9).SpecialCells(2).Resize(, 3)
For j = 2 To UBound(sn)
st = Split(Replace(sn(j, 1), "-", "/"), "/")
sn(j, 1) = Trim(st(0))
sn(j, 2) = Trim(st(1))
sn(j, 3) = Trim(st(2))
Next
Cells(1, 6).Resize(UBound(sn), 3) = sn
End Sub

AW: Da du ohnehin VBA verwenden willst, ...
09.03.2018 09:43:02
Albin
Hoi Luc
Danke vielmal für deine detaillierte Beschreibung / Erklärung. Dein Marko passt wie eine 1.
Da sieht man mal wieder, wie einfach die Lösung sein könnte.
Das mit dem Archiv ist so eine Sache. Natürlich suche ich immer vorgängig im Netz nach einer Lösung. Aber vielmals sieht man den Wald vor lauter Bäume nicht mehr.
Ich danke Dir nochmals und wünsche Dir ein schönes Weekend.
LG
Albin
Anzeige
Du meinst sicher nicht mich, ...
09.03.2018 16:12:29
Luc:-?
…Albin,
da dein Beitrag 1. unter dem von snb steht, du 2. nicht nach Links gefragt hast und ich auch nicht annehme, dass du selbst die erwähnten UDFs gesucht und auch gefunden hast, sowie du auch 3. Makro und nicht UDF schreibst. ;-]
Die Subprozedur (Makro) von snb ist im Ggsatz zu meinen UDFs sehr speziell und nur auf deinen Fall zugeschnitten, was bei einem solchen Makro auch legitim ist. Das kannst du dann auch in deines integrieren. Meine UDFs hättest du in der für Fktt üblichen Form in deinen Pgmm verwenden können.
SchöWE dito, Luc :-?
Anzeige

Forumthreads zu verwandten Themen

Frage - Spalten einfügen & Spalten untereinander z
André

Hallo zusammen!
Ich habe da als blutiger Anfänger in VBA zwei Fragen:
1.) Wie kann ich in meiner Tabelle in jeder zweiten Spalte 4 leere Spalten einfügen lassen?
2.) Dann möchte ich unter den Inhalt der ersten fünf Spalten (A-E) den Inhalt der zweiten 5 Spalten (F-J) usw. In der grö...
08.08.2008 16:00:00
Thread-ID: 1000340
3 Beiträge
Zeilenmerkmale in Spalten umwandeln
MG_Learning

Hallo zusammen!
Ich habe keine Ahnung, wie ich aus folgender Tabellenstruktur: Beispiel:
A B C
London 20 Kinos
London 50 Gaststätten
London 10 Einkaufscentren
Paris 18...
17.08.2008 18:31:50
Thread-ID: 1002449
4 Beiträge
Spalten in Zeilen anhängen
Jörg

Hallo zusammen,
in einer Tabelle mit ~1000 Artikeln in Spalte A stehen jeweils in den Spalten B-M die 12 zum Artikel gehörenden Monatswerte des Verbrauchs.
Ich brauche diese Daten jetzt so angeordnet, dass weiterhin alle Artikel in Spalte A stehen, jedoch die einzelnen Monatswerte unter...
01.09.2008 18:03:42
Thread-ID: 1006570
11 Beiträge
Anzeige
Zelleninhalt (Text) in 2 Spalten teilen
El.

Hallo!
Habe eine Spalte in der Artikelname und Artikelmerkmale, durch drei Punkte getrennt "...", zusammen stehen. Beispiel: Lecker Whisky 21 Jahre ... Lieferung in hochwertiger Schatulle
Wie krieg ich nun den Text links der Punkte in eine extra Spalte und rechts der Punkte in eine extr...
03.09.2008 16:35:00
Thread-ID: 1007155
4 Beiträge
wie Spalten in einem Arbeitsblatt zu Zeilen zusamm
Michael

Moin erstmal,
folgendes Problem als mittelmäßiger Anwender.
Ich habe mehrere identische Arbeitsblätter wo ich jeweils untereinander (eine Spalte) mehrer Werte habe. G1-G10.
Diese Werte möchte ich in einem Arbeitsblatt zusammenfassen , allerdings nun in einer Zeile. A1-J1.
Wi...
04.09.2008 19:48:05
Thread-ID: 1007497
4 Beiträge
Spaltentrennzeichen nach speichern in Text-Datei
Michael

Hallo
ich habe ein Problem. Vielleicht ist es schwierig, vielleicht aber auch nicht.
Ich habe eine Text-Datei mit Spaltentrennzeichen "|" (besser als ";" der Übersichtlichtlichkeit halber) und verschiedenen Längen der Spalten.
Ich will diese in Excel überarbeiten und als Text-Dat...
11.09.2008 09:28:00
Thread-ID: 1008978
3 Beiträge
Text in Spalten
Simone

Hallo zusammen,
ich hab ein wohl eher triviales Problem, komme aber trotzdem einfach nicht weiter.
Ich füge in ein Arbeitsblatt Daten aus einem anderen Programm ein. Jetzt habe ich mehrmals die Funktion 'Text in Spalten' verwendet / ausprobiert, und wenn ich jetzt wieder Daten in A1 ein...
16.09.2008 13:54:00
Thread-ID: 1010092
3 Beiträge
Anzeige
Zeilen löschen in Abhängigkeit von 2 Spalten
2

Hallo zusammen,
ich habe ein Makro über den Rekorder aufgezeichnet und versuche es anzupassen. Leider kenn ich mich in der Syntax nicht aus.
Ziel:
In einem markierten Bereich die Zeilen löschen, die in Abhängigkeit von zwei Spalten eine 0 enthalten.
D.h. Spalten X und Y enthalte...
06.10.2008 14:00:00
Thread-ID: 1014434
3 Beiträge
TextBox Werte in Spalte Q ersetzen ?
Selma

Hallo Leute,
ich habe eine Frage zu UserForm. Beim Klick auf CommandButton1 sollen die Zahlen aus TextBox1 mit Werten in Spalte Q ab Zeile 12 ersetzt werden. Dies soll nur dann erfolgen, wenn in TextBox1 die Zahlen stehen bzw. der TextBox gefüllt ist.
Ersetzt sollen nur die vorhandene W...
13.10.2008 15:42:00
Thread-ID: 1016083
3 Beiträge
Text in Spalten
Tom

Hallo,
ich möchte mit der Funktion Text in Spalten erreichen, dass alle Zeilen, die mit einer Zahl beginnen, nach der Zahl in eine neue Spalte geschoben werden - nur finde ich hierfür nur die Funktionen nach Leerzeichen, Komma usw...
Wer hat ne Lösung?
In A1: 1020 Maier Huber Mülle...
20.10.2008 14:09:42
Thread-ID: 1017654
7 Beiträge
Anzeige
Pivot - Text in Spalte überführen
Binu

Hallo
Ich hab in der Beispieldatei die Spalte "Zahl", welche ich in einer Pivotdatei ebenfalls als Spalte neben der Spalte "TExt" hinzufügen will. Nur kriege ich das nicht hin. Kann mir da jemand helfen? Besten Dank!
Gruss Albin
https://www.herber.de/bbs/user/56492.xls
04.11.2008 11:04:36
Thread-ID: 1021516
4 Beiträge
Text in Spalte nicht Spalte sondern Bereich
mehmet

Hallo Forum,
ich habe mit dem Makrorekorder "Text in Spalte" aufgezeichnet.
Wollte diese nun ändern für ein Bereich (Q4:Q60) statt Spalte (Q:Q).
Warum geht das nicht?
Private Sub CommandButton1_Click() ' Makro1 Makro ' Makro am 20.11.2008 von pc aufgezeichnet Sheets("Tabelle4"...
20.11.2008 17:10:38
Thread-ID: 1026492
3 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige