Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zelleninhalt teilen per VBA

Zelleninhalt teilen per VBA
16.07.2018 14:21:02
Testo
Hallo
würde gerne den Zelleninhalt teilen. Der Inhalt ist ohne Leerzeichen und gar nichts
Im Grunde Steht immer in Spalte A
Datum + Vier Buchstaben
180716TEST
Das Makro sollte dann in Spalte B neben der Spalte A (Also A1 dann B1, A2 dann B2)
das Datum eintragen
Am besten gleich im Datumsformat
Das würde heißen das nach dem Makro in Spalte A nur noch TEST drin steht und in Spalte B dann 16.07.18
Danke :)

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zelleninhalt teilen per VBA
16.07.2018 14:26:27
ChrisL
Hi
Sub t()
Dim c As Range
For Each c In Columns(1).SpecialCells(xlCellTypeConstants)
c.Offset(0, 1) = DateSerial(Mid(c, 5, 2), Mid(c, 3, 2), Left(c, 2))
c = Right(c, Len(c) - 6)
Next c
End Sub
cu
Chris
AW: Zelleninhalt teilen per VBA
16.07.2018 14:46:36
Testo
Danke funktioniert soweit
Habe nur was vergessen
In der Spalte kann auch was anderes drin stehen. z.b. Nur ein Text oder die Zelle kann auch ganz leer sein.
Das einzige Merkmal ist wenn der Zelleninhalt genau 10 Zeichen hat ohne Leerzeichen
Ich denke das ich diese mit einer If function lösen kann.
Wenn 10 Zeichen dann splitten. Wenn unter oder über 10 Zeichen dann zur nächsten Zeile
Anzeige
AW: Zelleninhalt teilen per VBA
16.07.2018 14:50:32
ChrisL
Hi
Sub t()
Dim c As Range
For Each c In Columns(1).SpecialCells(xlCellTypeConstants)
If Len(c) = 10 Then
c.Offset(0, 1) = DateSerial(Mid(c, 5, 2), Mid(c, 3, 2), Left(c, 2))
c = Right(c, Len(c) - 6)
End If
Next c
End Sub

cu
Chris
AW: Zelleninhalt teilen per VBA
16.07.2018 19:02:30
ChrisL
Hi
Oder so...
If Len(c) = 10 And IsNumeric(Left(c,6)) And not Instr(1,c," ") Then
cu
Chris
Lösung mit Fmln und ohne VBA
17.07.2018 14:49:43
Luc:-?
Auch wenn du eine erneute AW an Chris bisher schuldig geblieben bist, „Testo“,
will ich evtl weniger dir, aber ggf anderen Interessenten eine relativ einfache Fml-Alternative ohne VBA-Sub­Pro­ze­dur (man muss ja nicht für jeden Klacks eine eigene schreiben, vor allem, wenn man das nicht selber kann!) vorstellen. Die beiden, auf dein Problem passenden Fmln lauten wie folgt:
C1:=TEIL(A1;7-6*(D1="");99)
D1:=WENNFEHLER(--TEXT(--LINKS(A1;6);"2000-00-00");"")
Diese kann man in beliebigen (Hilfs-)Spalten parallel zu den Zeilen von Spalte A unterbringen (hier C und D). Anschließend könnte man ihre Ergebnisse als Werte auf A:B aufkopieren. Damit wären dann aber sowohl Fmln als auch Original­Werte weg.
Wollte man Letztere sicherheitshalber bewahren (und im NebenEffekt dann natürlich auch die Fmln) und trotzdem alles in den Spal­ten A:B realisieren, müssten die Original­Werte in die Fmln eingebettet wdn. Das kann man folgendermaßen erreichen (kann man auch mit einer VBA-Prozedur machen):
1. Parallel zu den Werten in A in B folgd Fml eintragen/-kopieren: ="#"""&A1&"""§"
2. Deren Ergebnisse kopieren und als Werte auf beide Spalten A:B aufkopieren, so dass dann in der jeweils 1.Zelle nur noch bspw #"180716TEST"§ steht.
3. Jetzt per Xl-Methode Ersetzen erst die §-Zeichen nach den OriginalWerten gg die rechten Fml-Teile austauschen, wobei die Fml für A etwas modifiziert wdn muss, um sie anpassungsfähig zu halten:
A-§;7-6*(index(B:B;zeile())="");99)
B-§;6);"2000-00-00");"")
4. Jetzt die #-Zeichen vor den OriginalWerten gg die linken Fml-Teile austauschen, unbedingt mit B beginnend, da Xl dann sofort rechnet (bei automatischer Berechnung) und sich die Fml in A auf B bezieht:
B-#=WENNFEHLER(--TEXT(--LINKS(
A-#=TEIL(
5. Die beiden Fmln in A:B geben jetzt das Gewünschte wieder und lauten bspw so:
A1:=TEIL("180716TEST";7-6*(INDEX(B:B;ZEILE())="");99)
B1:=WENNFEHLER(--TEXT(--LINKS("180716TEST";6);"2000-00-00");"")
Damit wären die OriginalWerte sogar 2× bewahrt und trotzdem die Daten nach Wunsch aufgeteilt worden. Sollen die OriginalWerte wiederhergestellt wdn, ist analog, aber in umgekehrter Reihenfolge zu verfahren.
Falls eine Wiederholung des Bezugs auf die OriginalWerte und damit deren mehrfache Wiederholung in der sie einschließenden Fml mal unumgänglich sein sollte, kann man sich auch mit einer einfachen „ErinnerungsFkt“ (VBA-gestützte UDF*) behelfen, die in der Fml das 1.Auftreten des Bezugs als Argument verwendet und dann ohne Argument statt aller anderen Bezüge auf den Original­Wert eingetragen wird.
Mit diversen UDFs aus dem Archiv lassen sich idR auch kompliziertere Fälle als dieser auf analoge Weise bewältigen, zB Notation eines Datums an beliebiger Position innerhalb eines Textes.
* Bspw UDF Rmd (Vs1.1): https://www.herber.de/forum/archiv/1592to1596/t1592543.htm#1592585
Feedback nicht unerwünscht! Gruß, Luc :-?
„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …
Anzeige
AW: Lösung mit Fmln und ohne VBA
19.07.2018 10:46:46
Rene
Sehr interessant.
Im Urlaub mal ausprobieren.
lg Rene
Viel Spaß - im Urlaub...! (s.a.unten) Gruß owT
19.07.2018 21:16:16
Luc:-?
:-?
AW: Zelleninhalt teilen per VBA
17.07.2018 15:35:31
Daniel
Hi
ich würde hier VBA mit Formeln kombinieren, dh auch per VBA würde ich die entsprechenden Formeln in die Zellen schreiben und dann die Formeln durch Werte ersetzen.
hat den Vorteil, dass es sehr schnell ist, außerdem einfach zu programmieren und zu testen (keine Schleifen, die man in Einzelstep durchtickern muss und man sieht sofort alle Ergebnisse, die man überprüfen kann.
With Range("A1:A" & Cells(rows.count, 1).end(xlup).row).Offset(0, 1).Resize(, 2)
.Columns(1).FormulaR1C1 = _
"=IF(LEN(SUBSTITUTE(RC1],"" "",""""))=10,RIGHT(RC1,4),RC1])"
.Columns(2).FormulaR1C1 = _
"=--IF(LEN(SUBSTITUTE(RC1,"" "",""""))=10,TEXT(--LEFT(RC1,6),""2000-00-00""),"""")"
.Formula = .Value
end with
Columns(1).Delete
funktioniert allerdings nur für Datumswerte ab Jahr 2000.
wenn es in der Spalte keine Ausnahmen gibt alle Zellen auf diese weise umgearbeitet werden müssten, ließe sich das auch sehr elegant mit TEXT IN SPALTEN erledigen. Allerdings werden dann alle Werte nach dem 6. Zeichen getrennt:
Columns("A:A").TextToColumns Destination:=Range("A1"), _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 5), Array(6, 1))

Gruß Daniel
Anzeige
AW: Zelleninhalt teilen per VBA
17.07.2018 18:26:04
ChrisL
Hi
Wäre schade, wenn die Lösung wegen Laufzeitfehler ignoriert würde... ;)
Ein Detail (eckige Klammer weg oder schliessen):
"=IF(LEN(SUBSTITUTE(RC1,"" "",""""))=10,RIGHT(RC1,4),RC1)"
cu
Chris
Warum 'schade', ...
18.07.2018 00:49:44
Luc:-?
…Chris?
Sind Daniels Fmln denn nun tatsächlich besser bzw aufs Problem passender als meine…? :-]
Wenn man Daniels SubProzedur auf meine TestDaten …
{"180716TEST";"XTest";"";180709;"";"alfa4711bet";"x180801y"}
…anwendet, erhält man folgd Ergebnis für die beiden Spalten:
{"TEST".43297;"XTest".#WERT!;0.#WERT!;180709.#WERT!;"".#WERT!;"alfa4711bet".#WERT!;"x180801y".#WERT!}
Leerzellen und Texte ohne Datumsangabe sollen aber vorkommen dürfen!
Außerdem sieht's ja nun nicht so aus, als ob das Testo überhpt zu würdigen weiß. Und für spätere Interessenten hat ja wohl gereicht, was bereits geantwortet wurde.
Dein Ergebnis wäre allerdings akzeptabler, hättest du nicht das Datum falschherum interpretiert:
{"TEST".42569;"XTest"."";""."";180709."";""."";"alfa4711bet"."";"x180801y".""}
Meine Fmln liefern dagg in allen genannten Fällen ein richtiges Ergebnis:
{"TEST".43297;"XTest"."";""."";"".43290;""."";"alfa4711bet"."";"x180801y".""}
Wenn man die Formeln wie angedeutet erweitert, wäre auch der letzte Wert sinnvoll interpretierbar, wie folgd Ergebnis zeigt:
{"TEST".43297;"XTest"."";""."";"".43290;""."";"alfa4711bet"."";"xy".43313}
🙈 🙉 🙊 🐵 Morrn, Luc :-?
„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …
Anzeige
AW: Warum 'schade', ...
18.07.2018 01:23:48
Daniel
HI Luc
mir ging es hautpsächlich darum aufzuzeigen, wie man auch in VBA-Makros mit Formeln arbeiten kann.
Insbesondere für Menschen mit dem Level "Excel gut - VBA bescheiden" dürfte das interessant sein, weil sie so für viele Aufgabenstellungen schnelle und einfache Lösungen erstellen können.
Man kann da auch jede andere Formel einsetzen, welche die Aufgabe löst (bei Matrixformeln wirds etwas aufwendiger), also gerne auch deine.
Ansonsten habe ich mich bei meinen Formeln an die Angaben von Testo gehalten:
"Das einzige Merkmal ist wenn der Zelleninhalt genau 10 Zeichen hat ohne Leerzeichen"
Gruß Daniel
Anzeige
Richtig, aber Testos 10 Zeichen wdn ja ...
18.07.2018 01:58:34
Luc:-?
…durch nachfolgende Äußerungen relativiert, Daniel;
aber das Wesentliche scheinen ja nunmal die 6 Ziffern am TextAnfang zu sein! Es ist bei dieser Schreib­weise eines Datums auch nicht anzunehmen, dass mal eine 0 fehlen dürfte. Folglich wäre das das einzig relevante Fixum — diese 6 Ziffern am TextAnfang. Danach könnten also beliebig lange Texte folgen, was bei einer Beschränkung auf 10 Zeichen insgesamt ggf kontraproduktiv wäre.
Wenn man nur ein PrinzipBsp liefert, sollte man das auch dazuschreiben, abgesehen mal davon, dass ich bereits erwähnt hatte, dass so etwas möglich ist. Interessanter wäre hier ein Pgm für den von mir gezeigten anderen Lösungsweg (mit Original­Daten­Erhalt) gewesen… ;-]
Uneingeschränkt interessant ist aber dein 2.Ansatz, auch wenn er hier wohl eher nicht brauchbar ist, aber in anderen Fällen ggf schon…
Im Übrigen aber bin ich der Meinung, dass wir wohl nie erfahren wdn, was Testo nun wirklich getan hat…
Gruß, Luc :-?
Anzeige
AW: Warum 'schade', ...
18.07.2018 08:46:33
ChrisL
Hi Luc
Weil sich Daniel die Zeit genommen hat einen anderen Lösungsansatz aufzuzeigen und weil ich grundsätzlich eine Vielfalt an Optionen als wertvoll erachte. Dies gilt natürlich auch für deinen Lösungsvorschlag Luc, aber bei dir habe ich keine eckige Klammer zum Kommentieren gefunden.
Schade aber auch, weil Testo vermutlich schon seit zwei Tagen nicht mehr mit liest.
Und ja, Daniels Formeln sind besser als deine Luc. Spass!!!
(das Bewerten von Lösungen überlasse ich anderen)
cu
Chris
So, so, du findest also nur positiv ...
18.07.2018 15:49:01
Luc:-?
…kommentarwürdig, Chris,
was einen offensichtlichen Flüchtigkeitsfehler enthält…‽ ;-]
Auch wenn du meinen Beitrag jetzt doch noch erwähnst, was meinst du wohl, wieviel Zeit ich mir für Erarbeitung und Test der beiden voll fktsfähigen gezeigten (und der nicht-gezeigten) Varianten, nebst Fml-Optimierung für die 2., genommen habe…?
Was Testo betrifft, Konsens (s. oben).
Gruß, Luc :-?
Anzeige
AW: So, so, du findest also nur positiv ...
18.07.2018 16:08:29
ChrisL
Hi Luc
Haha... dein Eingangskommentar ist lustig.
Aber wozu die Frage nach dem Zeitaufwand? Mehr Aufwand = besser Lösung? :P
Oder geht es dir vielleicht nur um die Würdigung deines Beitrages? Gut gemacht Luc! ;-]
cu
Chris
Du hattest Daniels Aufwand als einen ...
19.07.2018 20:49:05
Luc:-?
…2.Grund für deinen Beitrag angeführt, Chris;
aber sei's drum, das wirklich Interessante folgt unten… ;-)
Luc :-?
Interessanter Datumseffekt (2stell Jahr)
19.07.2018 21:13:46
Luc:-?
Im Zusammenhang mit Daniels Hinweis bzgl des Formats 2000-00-00 für 6stellige Datumsangaben der Form JJMMTT habe ich das mal näher untersucht und bin dabei auf einen interessanten Umwandlungseffekt gestoßen:
 DEFGHIJK
43
Datum/FmtStdrd (T·Zähler)JJMMTT00-00-00Stdrd (T·Zähler)JJ-MM-TTStdrd (T·Zähler)TT.MM.JJJJ16.07.201843 29718071618-07-1642 56916-07-1843 29716.07.201809.07.200137 08101070901-07-0939 99509-07-0137 08109.07.200129.02.200839 50708022908-02-2947 15729-02-0839 50729.02.200830.04.200939 93309043009-04-3011 05730-04-0939 93330.04.200931.05.200939 96409053109-05-3111 45231-05-0939 96431.05.2009 E44[:E48]:=D44 H44[:H48;J44:J48]:=--G44   F44[:F48;G44:G48;I44:I48;K44:K48]:=TEXT(E44;F$43)
44
45
46
47
48
49
50
Dieser tritt allerdings nur auf, falls ein derart angegebenes Datum auch einem regulären Datum der Form TT-MM-JJ entspräche. Für die Thematik dieses Threads würde das bedeuten, dass in solchen Fällen auch eine Fml wie …
=--TEXT(--TEXT(A1;"00-00-00");"JJ-MM-TT")
…benutzt wdn könnte. Ist das aber nicht möglich, müsste das Jahresformat ggf zwischen 1900 und 2000 variieren:
=--TEXT(A1;20-(--LINKS(A1;2)>29)&"00-00-00")
Damit würde ein solches 6stelliges Datum dann analog Xl-Automatismen als im 20. bzw 21.Jhdt liegend interpretiert wdn.
Luc :-?
Anzeige

314 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige