Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
908to912
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
908to912
908to912
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Zelleninhalte teilen (Trennzeichen "_") ohne .ext

Zelleninhalte teilen (Trennzeichen "_") ohne .ext
29.09.2007 14:12:00
Selma
Hallo Leute,
wie kann ich per VBA alle Zelleninhalte der Spalte N (ab Zeile 2) des aktives Arbeitsblattes in Spaltenbereich C:K splitten. Trennzeichen ist immer "_". Die letzten vier Zeichen (extension) sollen nicht mitberücksichtigt werden.
Die letzte Zeile mit Inhalt kann in Spalte A ermittelt werden.
Anbei die Beispieldatei (mit zwei Arbeitsblätter "Vorher" und "Nachher") um meine Frage zu verdeutlichen.
https://www.herber.de/bbs/user/46434.xls
Vielen Dank im Voraus.
Liebe Grüße
Selma

22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zelleninhalte teilen (Trennzeichen "_") ohne .ext
29.09.2007 15:29:58
Gerd
Hallo Selma,
probier mal.

Sub aufteilen()
Dim i As Long, j As Integer
For i = 14 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 3 To 10
Cells(i, j) = Split(Cells(i, 14), "_")(j - 3)
Next
Cells(i, j) = Split(Split(Cells(i, 14), "_")(j - 3), ".")(0)
Next
End Sub


Gruß Gerd

@ Gerd:
29.09.2007 21:07:07
{Boris}
Hi Gerd,
ich hatte zwar grad schon mal gepostet, aber der Beitrag ist nicht erschienen?!
Shit happens - again:
Du hattest bei meiner Frage ja auch mit der gleichen Syntax geantwortet - und es funktioniert einwandfrei. Nur versteh ich ehrlich gesagt diese Syntax nicht so ganz. Kannst Du mich kurz aufklären?
Grüße Boris

Anzeige
AW: @ Boris:
29.09.2007 21:35:35
Renee
Hi Boris,
Hat mich auch kurz irritiert.
Die hintere Klammer ist einfach der Index des Arrays den Split zurückgibt.
Die innere Schleife kann man sich aber sparen:
Der zweite Split ist nur um die rechte Hälfte ab dem Punkt loszuwerden.

Sub aufteilen()
Dim i As Long, j As Integer
For i = 14 To Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(i, 3), Cells(i, 10)) = Split(Cells(i, 14), "_")
Cells(i, 11) = Split(Split(Cells(i, 14), "_")(8), ".")(0)
Next
End Sub


GreetZ Renee

AW: @ Renee
29.09.2007 21:39:45
{Boris}
Hi Renee,
Die hintere Klammer ist einfach der Index des Arrays den Split zurückgibt
Das war das fehlende Puzzleteil. Vielen Dank für die Aufklärung!
Grüße Boris

Anzeige
Nochmal @ Renee
29.09.2007 21:47:32
{Boris}
Hi Renee,
mail mich doch mal über unsere Excelformeln-Kontaktmail an:
excelformeln(at)web(Punkt)de
Meine eigene Mail-Addi gebe ich hier nicht so gerne bekannt - geantwortet wird natürlich über meine Privatmail.
Natürlich nur, wenn Du Lust hast.
Grüße Boris

AW: Nochmal @ Renee
30.09.2007 00:42:53
Renee
Hi Boris,
Meine Mail s.u.l.a.w.e.y.o.(at).h.o.t.m.a.i.l.(dot).c.o.m ;-) Renee

Du hast Post oT
30.09.2007 20:20:00
{Boris}

AW: Zelleninhalte teilen (Trennzeichen "_") ohne .ext
29.09.2007 15:33:38
Daniel
HI
dafür gibst die Excelfunktion DATEN - TEXT IN SPALTEN.
das einfach mal von Hand ausführen und den Recorder dabei mitlaufen lassen.
als fertiges Makro siehts dann so aus:

Sub Test()
Dim ZeStart As Long
Dim ZeAnz As Long
ZeStart = 14
ZeAnz = Cells(65536, 1).End(xlUp).Row - ZeStart + 1
Application.DisplayAlerts = False
Cells(ZeStart, "n").Resize(ZeAnz).TextToColumns Destination:=Cells(ZeStart, "c"), _
other:=True, otherchar:="_"
Cells(ZeStart, "C").Resize(ZeAnz, 9).Replace ".pdf", ""
Application.DisplayAlerts = True
End Sub


gruß, Daniel

Anzeige
AW: Zelleninhalte teilen (Trennzeichen "_") ohne .
29.09.2007 16:15:43
Matthias
Hallo Selma,
mein Vorschlag: eine benutzerdef. Funktion (VBA).
siehe hier:
https://www.herber.de/bbs/user/46436.xls
Gruß Matthias

Danke Gerd, danke Daniel, danke Matthias
29.09.2007 16:33:00
Selma
danke Gerd, danke Daniel, danke Matthias
LG
Selma

AW: Zelleninhalte teilen (Trennzeichen "_") ohne .ext
29.09.2007 22:20:26
Peter
Hallo Selma,
wenn das .pdf IMMER vorkommt, dann so:

Sub Verteilen()
Dim lZeile  As Long
For lZeile = 2 To Range("A65536").End(xlUp).Row
If Len(Range("N" & lZeile).Value) > 4 Then
Range("C" & lZeile & ":K" & lZeile) = Split(Left(Range("N" & lZeile).Value, _
Len(Range("N" & lZeile).Value) - 4), "_")
End If
Next lZeile
End Sub


Gruß Peter

Anzeige
AW: Zelleninhalte teilen (Trennzeichen "_") ohne .
30.09.2007 01:11:07
Selma
Hallo Peter,
in der Beispieldatei waren nur PDF-Dateien. Es kommen aber auch andere Dateiformate vor.
LG
Selma

AW: Zelleninhalte teilen (Trennzeichen "_") ohne .
30.09.2007 01:35:00
Daniel
Hi
wenn noch andere Dateiformate vorkommen können, dann probier mal das hier aus:

Sub Test()
Dim ZeStart As Long
Dim ZeAnz As Long
ZeStart = 14
ZeAnz = Cells(65536, 1).End(xlUp).Row - ZeStart + 1
Application.DisplayAlerts = False
Cells(ZeStart, "n").Resize(ZeAnz).TextToColumns Destination:=Cells(ZeStart, "c"), _
other:=True, otherchar:="."
Cells(ZeStart, "c").Resize(ZeAnz).TextToColumns Destination:=Cells(ZeStart, "c"), _
other:=True, otherchar:="_"
Application.DisplayAlerts = True
End Sub


die Dateinamen werden erst in den Datei-Namen und die Erweiterung zerlegt und erst im zweiten Schritt dann in die Einzelbestandteile (getrennt durch "_").
Im zweiten Schritt wird die Datei-Erweiterung dann einfach überschrieben.
Gruß, Daniel

Anzeige
AW: Zelleninhalte teilen (Trennzeichen "_") ohne .
30.09.2007 14:30:26
Gerd
Hallo,
zur Frage von Boris habe ich in dessen Original_Thread geantwortet.
Interessant zu erfahren, dass ein Range sich die passende Anzahl an Elementen aus dem
Split-Variant-Array ziehen kann, danke an Renee.
So könnte man die Variante mit "Split" noch eine Zeile kürzen.

Sub aufteilen()
Dim i As Long
For i = 14 To Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(i, 3), Cells(i, 11)) = Split(Split(Cells(i, 14), ".")(0), "_")
Next
End Sub


Dennoch gefällt mir die Alternative von Daniel ohne Schleife besser. Die TextToColumns-Methode er-
kennt offensichtlich Zahlen u. formatiert diese entsprechend.
Sicher könnte man auch beides kombinieren, z.B. mit "Split" die Einträge aus Spalte N ohne Dateierweiterun nach Spalte C bringen u. von dort mit "TextToColumns" auf die Spalten C bis K
aufteilen.
Grüße Gerd

Anzeige
Die Entscheide/Resultate einer...
30.09.2007 15:55:35
Renee
Gruppe, sind meist besser und konsistenter als die Einzelner. Und v.a. kann jede von jedem und jeder von jeder profitieren ;-)
Hi Gerd,
Ja ich finde die Lösung von Daniel auch gut.
Logischerweise kann man aus allen Angaben zusammen vermuten, dass die Text-to-Column Funktion intern vermutlich wiederum mit der Split-Funktion arbeitet (oder zumindest, Teilen davon).
Da die Split-Funktion einen 0-basierten Array als Resultat zurückgibt ist es ebenfalls logisch, dass eine Range damit direkt angesprochen werden kann, da ja eine Range (bzw. die Values davon) auch nix anderes ist als ein 0-basierter Array.
GreetZ Renee

Anzeige
AW: Zelleninhalte teilen (Trennzeichen "_") ohne .
30.09.2007 17:26:42
Peter
Hallo Selma,
mein Makro erwartet lediglich ein 4-stelliges Kürzel (Suffix), egal wie das heißt.
Gruß Peter

AW: @Renee
30.09.2007 18:04:00
Gerd
Hi,
nur in einem Punkt ein ganz kleiner Widerspruch. :-)
Ein Range ist ein zweidimensionaler eins-basierter Variant.
Gruß Gerd

Einspruch stattgegeben... owT
30.09.2007 18:06:40
Renee

AW: Zelleninhalte teilen (Trennzeichen "_") ohne .
30.09.2007 20:14:29
Selma
Hallo Peter,
das habe ich erst später gesehen ;)
Danke.
LG
Selma

AW: Zelleninhalte teilen (Trennzeichen "_") ohne .
30.09.2007 20:32:06
Selma
Ich stelle fest, dass das Makro "Test" von Daniel in der Beispieldatei in Spalte I eine "6" einträgt, anstatt eine "06".
Ist die Formatuerung der Zelle falsch, oder ?
LG
Selma

Anzeige
AW: Zelleninhalte teilen (Trennzeichen "_") ohne .
01.10.2007 00:13:08
Gerd
Hallo Selma,
Du kannst die Fieldinfo-Argumente entsprechend einstellen.

Sub Test()
Dim ZeStart As Long
Dim ZeAnz As Long
ZeStart = 14
ZeAnz = Cells(65536, 1).End(xlUp).Row - ZeStart + 1
Application.DisplayAlerts = False
Cells(ZeStart, "n").Resize(ZeAnz).TextToColumns Destination:=Cells(ZeStart, "c"), _
Other:=True, OtherChar:="."
Cells(ZeStart, "c").Resize(ZeAnz).TextToColumns Destination:=Cells(ZeStart, "c"), _
Other:=True, OtherChar:="_", _
Fieldinfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array _
(6, 1), Array(7, xlTextFormat), Array(8, 1), Array(9, 1))
Application.DisplayAlerts = True
End Sub


Gruß Gerd

Anzeige
Super Gerd... Danke
01.10.2007 00:35:17
Selma
Super Gerd... Danke

306 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige