Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 09:26:26
Median für Zeilen mit gleichen Zeitstempel
19.04.2024 08:41:23
Zwei Makros in ein Arbeitsblatt - zweite wird nicht ausführt
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1248to1252
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1248to1252
1248to1252
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Kennzeichen trennen

Kennzeichen trennen
walter
Hallo zusammen,
habe nochmal eine Frage:
Ich möchte die Kennzeichen zerlegen.
In der Spalte 8 stehen die Kennzeichen.
Jetzt möchte ich die Ziffern rechts vom 1. Buchstaben trennen.
Beispiel:
HH-KJ1234
Soll dann in : HH-KJ 1234
stehen. Hatte das unten stehende ebenfalls vom Forum, leider werden sehr
häufig nur die letzten 3 nach rechts geschoben und die 4 Ziffer bleibt dann
am letzten Buchstaben kleben.
Sub Kennzeichen_Zahlen_Trennen()
Dim rngCell As Range
ActiveSheet.Unprotect (getStrPasswort)
For Each rngCell In Columns(8).SpecialCells(xlCellTypeConstants)
rngCell = Trim(rngCell)
rngCell = Left$(rngCell, Len(rngCell) - Len(CStr(Val(StrReverse(rngCell))))) & " " & _
Right$(rngCell, Len(CStr(Val(StrReverse(rngCell)))))
Next
mfg walter mb

51
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Kennzeichen trennen
Rudi
Hallo, Sub Kennzeichen_Zahlen_Trennen() Dim rngCell As Range, i As Integer 'ActiveSheet.Unprotect (getStrPasswort) For Each rngCell In Columns(8).SpecialCells(xlCellTypeConstants) i = 1 rngCell = Replace(rngCell, " ", "") Do While Not IsNumeric(Mid(rngCell, i, 1)) i = i + 1 Loop rngCell = UCase(Left$(rngCell, i - 1)) & " " & Mid(rngCell, i) Next End Sub
Gruß
Rudi
AW: Kennzeichen trennen mit Formel
Andre´
Hallo,
test

 HI
1HH-KJ1234HH-KJ 1234

Formeln der Tabelle
ZelleFormel
I1{=LINKS(H1;LÄNGE(H1)-LÄNGE(SUMME((TEIL(0&H1;KGRÖSSTE(WENN(ISTZAHL(TEIL(0&H1;ZEILE($1:$256); 1)*1); ZEILE($1:$256); 1); ZEILE($1:$256)); 1)*1)*10^(ZEILE($1:$256)-1))))&" "&SUMME((TEIL(0&H1;KGRÖSSTE(WENN(ISTZAHL(TEIL(0&H1;ZEILE($1:$256); 1)*1); ZEILE($1:$256); 1); ZEILE($1:$256)); 1)*1)*10^(ZEILE($1:$256)-1))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
MFG Andre
Anzeige
Werde gleich informieren
walter
klappt nicht
walter
Hallo Andre,
habe versucht die Formel zu kopieren, bleibt hier stehen:
;ZEILE..
ich fange bei Zeile 4 an !
mfg Walter
VBA erlaubt ?
Matthias
Hallo
Liege ich richtig in der Annahme das Du es so willst
Tabelle1

 ABC
1DD-H2468DD-H2468
2wAR-kk1234wAR-kk1234
3H-TT9 060H-TT 9060


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Darfs den ein bisschen VBA sein?
denn genau damit (VBA) habe ich es so geteilt.
Gruß Matthias
Anzeige
AW: VBA erlaubt ?
walter
Hallo Matthias,
das Kennzeichen soll nicht in 3 Spalten aufgeteilt werden,
sondern es soll nur ein Leerzeichen eingefügt werden.
natürlich auch als Makro,
danke im Voraus,
mfg walter mb
Noch eine Nachfrage
Matthias
Hallo
Wie liegen z.B. die Kennzeichen von Berlin (B) oder Köln (K) denn vor?
Bitte mal verschieden vorliegende Beispiele posten.
Es gibt da ja nur einen Buchstaben! Also keinen Bindstrich (Minus)
Ich meine welche verschiedenen Varianten kommen bei Dir vor?
Und dann schreibst Du es soll nur ein Leerzeichen eíngefügt werden
also aus
H-TT9 060 soll dann H-TT 9 060 werden ? (da habe ich nur vor der 9 ein Leerzeichen eingefügt!)
oder doch lieber so: H-TT 9060 ? (also ohne Leerzeichen zwischen den Zahlen)
Du siehst wie wichtig es ist die Frage richtig zu formulieren!
Gruß Matthias
Anzeige
AW: Noch eine Nachfrage
walter
Hallo Matthias,
so sollte das Ergebnis sein: H-TT 9060
nach dem letzten BUCHSTABEN ein Leerzeichen, danach ergibt sich die zusammenhängende
Zahl.
Egal ob die 1, 2, 3 oder 4 Stellig ist !
mfg walter mb
dann muss ich auch nix mehr nachfragen ;o) oT
Matthias
Das versteh ich nicht mfg Walter
walter
Hallo Andre,
das ist perfekt, geht das auch mit Makro ?
mfg Walter mb
per Makro, da muss ich passen... o.T
Andre´
Vielleicht hat noch jemand eine Idee
walter
Hallo zusammen,
vielleicht hat jemand eine Idee diese Formel mit einem Makro verpacken.
mfg Walter mb
Frage noch offen oT.
Andre´
Rückfrage
Erich
Hi Walter,
vor einer VBA-Umsetzung stellt sich immer noch die Frage, was genau herauskommen soll.
Hier ein paar Beispiele. Die grünen sind vermutlich klar, bei den anderen: Was soll dann sein?
(In Zeile 1 ist noch der mit dem angehängten Leerzeichen...)
 ABCDEF
1H-TT9060 H-TT9 060 H-TT 9060FALSCH109
2H-TT9060H-TT 9060H-TT 9060WAHR  
3aa a WAHR  
4aaaaaa aaa WAHR  
5a1a 1a 1WAHR  
6a123456789a 123456789a 123456789WAHR  
7a123 b456 c789a123 b456 c 789a123b456c 789FALSCH1513
8a-b-c567a-b-c 567a-b-c 567WAHR  
9ABCDE12345ABCDE 12345ABCDE 12345WAHR  
10a1234 56 789a1234 56  789a 123456789FALSCH1311
11123#WERT!#WERT!#WERT!#WERT!#WERT!
12a b c 123 456 789a b c 123 456  789abc 123456789FALSCH1813

Formeln der Tabelle
ZelleFormel
B1{=LINKS(A1;LÄNGE(A1)-ANZAHL(RECHTS(A1;SPALTE(1:1))*1))&" "&RECHTS(A1;ANZAHL(RECHTS(A1;SPALTE(1:1))*1))}
C1{=LINKS(WECHSELN(B1;" ";""); LÄNGE(WECHSELN(B1;" ";""))-ANZAHL(RECHTS(WECHSELN(B1;" ";""); SPALTE(1:1))*1))&" "&RECHTS(WECHSELN(B1;" ";""); ANZAHL(RECHTS(WECHSELN(B1;" ";""); SPALTE(1:1))*1))}
D1=B1=C1
E1=LÄNGE(B1)
F1=LÄNGE(C1)
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich
Anzeige
Dann lasse ich es so, wenn es kein ... -)
walter
Guten Morgen Erich,
ich werd das so mit deiner Formel durchführen.
Werde eine Spalte einfügen und die Formel runterziehen, danach die
Werte in die richtige Spalte einfügen.
Schönen Freitag noch,
mfg Walter MB
doch mal mit VBA
Erich
Hi Walter,
vielleiocht ist diese VBA-Lösung ja noch ein wenig praktikabler für dich: 

Option Explicit
Sub aTest()
Dim rngCell As Range
'           -----------VVVVVVVVVV----------- hier Bereich anpassen
For Each rngCell In Columns(2).SpecialCells(xlCellTypeConstants)
rngCell = Ziff_Rechts_Trenn(rngCell)
Next rngCell
End Sub
Function Ziff_Rechts_Trenn(ByVal strT As String) As String
Dim ii As Integer, intA As Integer
strT = RTrim(strT)
For ii = Len(strT) To 1 Step -1
intA = Asc(Mid(strT, ii, 1))
Select Case intA
Case 48 To 57                 ' Ziffer 0 bis 9
Case 32                       ' Leerzeichen
Ziff_Rechts_Trenn = strT
Exit Function
Case Else                     ' anderes Zeichen
Ziff_Rechts_Trenn = Left(strT, ii) & " " & Mid(strT, ii + 1)
Exit Function
End Select
Next ii
Ziff_Rechts_Trenn = strT
End Function
Die Fkt. kannst du auch als UDF in Tabellenblättern verwenden.
Schönes Wochenende! Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich
Anzeige
Perfekt !!! --))
walter
Hallo Erich,
habe gerade das Makro getestet, einwandfrei !
Danke und schönes Wochenende,
mfg Walter mb
AW: Perfekt ? ABER...
Andre´
Hallo Walter,
das Marko funktioniert aber nicht, wenn z.B. ein Leerzeichen zwischen den Zahlen steht, so wie es nach Deiner Schilderung auch vorkommen kann!
z.B. wird aus
H-TT9 060
nicht
H-TT 9060
bleibt also unverändert!
MFG Andre
AW: Perfekt ? ABER...
Walter
Hallo André,
Bei mir werden die Ziffern an einem Stück geschrieben also
deshalb klappt es bei mir.
Aber sehr schön das Du Dir Gedanken gemacht hast.
Kann man den das Makro ergänzen? Vorsichtshalber ?
Mit freundlichen Grüßen Walter mb
Anzeige
AW: Kennzeichen trennen - hier mal meine Version
Andre´
Hallo zusammen,
das ganze funktioniert, solange keine Leerzeichen zwischen den Buchstaben stehen :-))
MFG Andre
Richtig erkannt !
Matthias
Hallo
Das ist mir bewusst!
Deshalb hatte ich ja auch am 09.02.2012 19:15:50 nachgefragt welche möglichen Varianten vorliegen!
Leider wurde mir das nicht beantwortet
Gruß Matthias
ja, Walter war ja auch schon zufrieden oT
Andre´
ich aber nicht mit seiner Antwort ...
Matthias
Hallo
Mein Bsp sollte auch nur als Ergänzung oder Alternative zu verstehen sein ;o)
Hier mal meine andere Version:
https://www.herber.de/bbs/user/78851.xls
Gruß Matthias
Anzeige
na BINGO :-))
Andre´
Hi,
mal sehen ob sich Walter diesbezüglich noch meldet.
MFG Andre
Guten Morgen Andre
walter
Guten Morgen Andre,
wenn ichg schon eine Hilfe benötige, ist es für mich selbstverständlich zu helfen.
Das Makro von Matthias, klappt nicht so Recht.
mfg Walter mb
Guten Morgen Andre
walter
Guten Morgen Andre,
wenn ich schon eine Hilfe benötige, ist es für mich selbstverständlich zu melden.
Das Makro von Matthias, klappt nicht so Recht.
mfg Walter mb
Guten Morgen Matthias
walter
Guten Morgen Matthias,
konnte gestern Abend leider nicht mehr.
Habe das Makro auf meine Spalte 8 verändert, das ist das Ergebnis:
MG-YT193G-YT193-YT193YT193T193 193933
mfg deshalb hatt ich das Ergebnis von Erich als soweit gut befunden, da ich nicht der
Fachmann bin, leider, sondern nur Hilfe übers Forum hole.
Wie gesagt, die Ziffern stehen von RECHTS gesehen bis zum 1. Buchstaben
OHNE Leerzeichen !
Stelle nochmal Frage als offen.
mfg Walter mb
Anzeige
Guten Morgen Matthias
walter
Guten Morgen Matthias,
konnte gestern Abend leider nicht mehr.
Habe das Makro auf meine Spalte 8 verändert, das ist das Ergebnis:
MG-YT193G-YT193-YT193YT193T193 193933
mfg deshalb hatt ich das Ergebnis von Erich als soweit gut befunden, da ich nicht der
Fachmann bin, leider, sondern nur Hilfe übers Forum hole.
Wie gesagt, die Ziffern stehen von RECHTS gesehen bis zum 1. Buchstaben
OHNE Leerzeichen !
Stelle nochmal Frage als offen.
mfg Walter mb
AW: Guten Morgen Matthias
Hajo_Zi
Hallo Walter,
warum benutzt Du nicht Andre seinen Vorschlag, ganz ohne VBA?
Tabelle1
 HI
4HH-KJ1234HH-KJ 1234

verwendete Formeln
Zelle Formel Bereich
I4 {=LINKS(H4;LÄNGE(H4)-LÄNGE(SUMME((TEIL(0&H4;KGRÖSSTE(WENN(ISTZAHL(TEIL(0&H4;ZEILE($1:$256); 1)*1); ZEILE($1:$256); 1); ZEILE($1:$256)); 1)*1)*10^(ZEILE($1:$256)-1))))&" "&SUMME((TEIL(0&H4;KGRÖSSTE(WENN(ISTZAHL(TEIL(0&H4;ZEILE($1:$256); 1)*1); ZEILE($1:$256); 1); ZEILE($1:$256)); 1)*1)*10^(ZEILE($1:$256)-1))} $I$4
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Tabellendarstellung in Foren Version 5.48


Gruß Hajo
Anzeige
An ALLE, ich auch getestet aber -)
walter
Guten Morgen Hajo und alle die geholfen haben,
ich habe das Makro vom Erich genommen, weil ich so keine Spalte einfügen brauch etc.,
daher für mich gut.
Alle anderen habe ich ja ebenfalls getestet, die Formel von Andre klappt ebenfalls
klasse.
Habe ich auch geschrieben.
Ich finde es toll so eine Menge und GROße Unterstützung zu erhalten,
tausend DANK.
Ich wünsch allen ein schönes Wochenende,
mfg walter mb
Leider Fehler
walter
Guten Abend Rudi,
Fehler, bleibt hier stehen:
i = i + 1
Laufzeitfehler 6
mfg walter mb
noch eine Formel
Erich
Hi Walter,
oder diese Formel?
Tabelle1

 AB
1HH-KKHH-KK
2HH-KK3HH-KK 3
3HH-KK34HH-KK 34
4HH-KK345HH-KK 345
5HH-KK3456HH-KK 3456
6HH-KK34567HH-KK 34567

Formeln der Tabelle
ZelleFormel
B1{=LINKS(A1;LÄNGE(A1)-ANZAHL(RECHTS(A1;SPALTE(1:1))*1))&" "&RECHTS(A1;ANZAHL(RECHTS(A1;SPALTE(1:1))*1))}

(ist von http://www.excelformeln.de/formeln.html?welcher=101 )
Die könntest du auch in VBA einbauen.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich
AW: noch eine Formel
walter
Hallo Erich,
ist die Formel auch für Kennzeichen die unterschiedliche Anzahl von
Buchstaben haben ?
z.B DD-H1234
wAR-kk1234
mfg Walter mb
AW: noch eine Formel
Bernd
Hallo Walter,
probier's doch einfach mal aus ... bei mir funktioniert es auf jeden Fall!
Gruß
Bernd
nicht ganz
walter
Hallo Erich,
hallo Bernd,
habe gerade die Formel eingesetzt, leider wird die 4 Zahl nicht getrennt.
H-TT9060
H-TT9 060
mfg walter mb
oder doch?
Erich
Hi Walter,
das liegt wohl weniger an der Formel als an deinem String - der endet mit einem Leerzeichen,
wie sich hier zeigt:
 ABCDE
1H-TT9060 H-TT9 060 H-TT9060#9H-TT 9060
2H-TT9060H-TT 9060H-TT90608H-TT 9060

Formeln der Tabelle
ZelleFormel
B1{=LINKS(A1;LÄNGE(A1)-ANZAHL(RECHTS(A1;SPALTE(1:1))*1))&" "&RECHTS(A1;ANZAHL(RECHTS(A1;SPALTE(1:1))*1))}
C1=WECHSELN(A1;" ";"#")
D1=LÄNGE(A1)
E1{=LINKS(A1;LÄNGE(GLÄTTEN(A1))-ANZAHL(RECHTS(GLÄTTEN(A1); SPALTE(1:1))*1))&" "&RECHTS(GLÄTTEN(A1); ANZAHL(RECHTS(GLÄTTEN(A1); SPALTE(1:1))*1))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Will man Leerzeichen auch erledigen können, dann z. B. mit der Formel in E1. Aber besser bereinigt man die Daten vorher...
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich
Ich hätte es ganz genau so gemacht ;o)
Matthias
Hi Andre
Woher dieser String MG-YT193G-YT193-YT193YT193T193 193933 bei Walter kommt,
weiß ich auch nicht.
Gruß Matthias
Woher dieser String, kann ich Dir sagen
Andre´
Hallo Matthias
Walter hat wohl an jeder Stellen in Deinem Code wo eine 1 stand eine 8 gesetzt, dann kommt dieser Murks zustand:-))
MFG Andre
schon möglich, aber MG-YT ? ...
Matthias
Hallo Andre
... in meiner Bsp.Datei war aber kein Kennzeichen mit MG-YT dabei!
Nur das hatte mich gewundert.
Vielleicht schaut ja Walter doch nochmal rein.
Jedenfalls hat mein Makro funktioniert, das hat mir Deine Spaltenanpassung ja gezeigt
schönes Restwochenende wünsche ich :o)
Gruß Matthias
AW: schon möglich, aber MG-YT ? ...
Walter
Hallo André und Matthias,
Kann heute nicht mehr arbeiten aber morgen Früh. Ja das stimmt ich habe
Anstelle der 1 ein 8 gesetzt, Sage morgen Früh Bescheid,
Herzliche Grüße
Walter mb
AW: schon möglich, aber MG-YT ? ...
Walter
Hallo André und Matthias,
Kann heute nicht mehr arbeiten aber morgen Früh. Ja das stimmt ich habe
Anstelle der 1 ein 8 gesetzt, Sage morgen Früh Bescheid,
Herzliche Grüße
Walter mb
So jetzt die Info, Klasse aber
walter
Guten Morgen Matthias und Andre,
natürlich klappt das einwandfrei von ANDRE !!!
Das einzige ist die Länge der Zeilen, die Länge sollte nach Spalte "B"
eingestellt sein, so brauch ich nicht ändern,
wenn MÖGLICH !!!
mfg walter mb
das war von Anfang an korrekt, Walter
Anfang
Hallo
Du hast nur die Spalte - übrigens( in meinem Code ) - nicht richtig angepasst
ANDRE !!! hat das dann getan. So sieht das aus.
Also 3 Ausrufezeichen, das solltest Du unterlassen.
Das untergräbt ein bisschen meinen Einsatz im Beitrag, denn den Code habe ich geschrieben. ;-)
Trotzdem liebe Grüße auch an ANDRE.
Zitat:
wenn MÖGLICH !!!
Antwort:
Ja ist möglich:
Sub Teilen()
Dim Idx&, Strg$, Buchstabe$, Zahl$, X&, Loletzte&
Loletzte = Cells(Rows.Count, 2).End(xlUp).Row
For X = 1 To Loletzte
Buchstabe = ""
Zahl = ""
Strg = Cells(X, 8).Text
For Idx = 1 To Len(Cells(X, 8).Text)
If Not IsNumeric(Mid(Strg, Idx, 1)) Then
Buchstabe = Buchstabe & Mid(Strg, Idx, 1)
Buchstabe = Trim(Buchstabe)
Else
Zahl = Zahl & Mid(Strg, Idx, 1)
Zahl = Trim(Zahl)
End If
Next
Cells(X, 8).Value = Trim(Buchstabe & " " & Zahl)
Next
End Sub
Gruß Matthias
Tausend und entschuldige Matthias -)
walter
Hallo Matthias,
entschuldige bitte, bin mit den vielen Mitteilungen einfach durcheinander gekommen.
Tausend Dank,
klappt alles, habe gerade getestet !
Schönen Sonntag noch und auch selbstverständlich DANKE an Andre !
mfg walter mb
AW: So jetzt die Info, Klasse aber
Andre´
Hallo Walter,
die Werte hast Du aber in der Spalte H stehen, die bearbeitet werden sollen.
Warum möchtest Du dich jetzt auf Spalte B bezüglich der Länge beziehen?
Wenn in der Spalte B weniger Zeilen sind dann wird auch nur bis zu dieser Zeile der Spalte H das Marko laufen!
Ich würde dann den Code von Matthias!!! bezieht sich auf
https://www.herber.de/forum/messages/1250286.html
dann so einsetzen!
Sub Teilen()
Dim Idx&, Strg$, Buchstabe$, Zahl$, X&, Loletzte&
'Loletzte = Cells(Rows.Count, 2).End(xlUp).Row ' Spalte B
Loletzte = Cells(Rows.Count, 8).End(xlUp).Row  ' Spalte H
For X = 1 To Loletzte
Buchstabe = ""
Zahl = ""
Strg = Cells(X, 8).Text
For Idx = 1 To Len(Cells(X, 8).Text)
If Not IsNumeric(Mid(Strg, Idx, 1)) Then
Buchstabe = Buchstabe & Mid(Strg, Idx, 1)
Buchstabe = Trim(Buchstabe)
Else
Zahl = Zahl & Mid(Strg, Idx, 1)
Zahl = Trim(Zahl)
End If
Next
Cells(X, 8).Value = Trim(Buchstabe & " " & Zahl)
Next
End Sub
MFG Andre
Auch Dir danke -)
walter
Hallo Andre,
ich finde es Klasse an was ihr alles denkt.
Es können schon mal "unten" keine Kennzeichen stehen, deshalb die
LÄNGE aus der Spalte A.
Ihr auch Dir DANKE für die Unterstützung,
schönen Sonntag noch,
mfg walter mb
Wow, deshalb die LÄNGE aus der "Spalte A" oT :o)
Matthias
Walter hat viele Lösungsansätze....
Andre´
Hi Matthias,
bekommen,
Ich denke, jetzt kann er Deinen Code auch selbst anpassen:-))
MFG Andre

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige