Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1000to1004
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
Inhaltsverzeichnis

Teile von Zahlen auslesen und einfügen

Teile von Zahlen auslesen und einfügen
08.08.2008 22:57:00
Zahlen
Hallo Leute,
ich habe ein Problem mit Zahlenformaten.
In meiner Tabelle steht der Text '4917234598' (natürlich viele solcher Zahlen untereinander.
Jetzt möchte ich, daß diese Zahl (später eine ganze Spalte) über Makro so aufbereitet wird, daß aus '4917234598' 017234598 wird.
Bisher habe ich nachstehendes zusammengezimmert, kriege aber am Ende der Zahl das Apostroph nicht weg.
Vielleicht kann mir jemand auf die Sprünge helfen.
https://www.herber.de/bbs/user/54461.doc

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Teile von Zahlen auslesen und einfügen
08.08.2008 22:59:59
Zahlen
Hallo,
warum ein .doc und nicht die Mappe mit Daten?
Gruß aus’m Pott
Udo

AW: Teile von Zahlen auslesen und einfügen
08.08.2008 23:49:57
Zahlen
uuaaaaahhh, das ist ja feinster GruselCode nach Hasenrodtscher Definiton.
bevor du weiter machst, bitte erstmal das hier gründlich lesen:
http://www.online-excel.de/excel/singsel_vba.php?f=78
http://www.online-excel.de/excel/singsel_vba.php?f=4
ansonsten verwende lieber Excelformeln, die sind für diese aufgabe mindestens genauso gut geeignet wie dein "Makro"

=Wenn(länge(A1)


diese Formel eintragen (Zellbezug natürlich anpassen) und nach unten kopieren und du bist wahrscheinlich schneller sogar schneller als mit dem Makro (bei grossen Datenmengen zumindest)
je nachdem ob das erste Hochkomma zum String dazugezählt wird oder als "Textkennungszeichen" interpretiert wird musst du für den 2. Fall die Formel so abändern:


=Wenn(länge(A1)


dann anschießend noch kopieren und mit INHALTE EINFÜGEN - WERTE die Originaldaten ersetzen, dann hast du den gleichen Effekt wie dein Makro
Gruß, Daniel

Anzeige
Peter ist ein Hase
09.08.2008 00:00:30
Uduuh
Hallo Daniel,
kein Hasen
Peter Haserodt.
;-)
Gruß aus’m Pott
Udo

AW: Teile von Zahlen auslesen und einfügen
09.08.2008 08:37:40
Zahlen
Hallo
Benutz doch Daten Text in Spalten
Userbild
Aktiviere bei Trennzeichen "Andere" und wähle ein Hochkomma.
In einer weiteren Spalte ersetzt Du dann mit der Formel die 49 durch eine 0 (Null).
Gruß Matthias

AW: Teile von Zahlen auslesen und einfügen
10.08.2008 08:40:00
Zahlen
Hallo Matthias,
diese Ideee in Excel hatte ich schon. Ich möchte das Ganze aber als Marko schreiben und da habe ich mein Problem.
Grund hierfür: Viele Tel.-Nr. stehen untereinander (das können mal mehr oder weniger sein). Es nervst ständig die Formel eingeben zu müssen, deshalb die Idee mit dem Marko.
Vieelicht hast du oder noch jemanden eine zündende Idee?
Bernie

Anzeige
AW: Teile von Zahlen auslesen und einfügen
10.08.2008 10:57:00
Zahlen
Hi
Variante 1 du schreibst ein Makro, daß die Formel einsetzt
Variante 2 du schauts dir an, wie die Formel arbeitet und überträgst das in dein Makro
Die Textfunktion "TEIL()" heist in VBA "MID$()"
das ganze könntest du aber auch viel leichter über BEARBEITEN - ERSETZEN lösen.
erst alle '49 durch 0 ersetzen und dann alle ' durch nichts.
sieht als Code so aus:

Sub testerlei()
Cells.NumberFormat = "@"
With Cells.SpecialCells(xlLastCell).EntireColumn
.Replace What:="'49", Replacement:="0", lookat:=xlPart
.Replace What:="'", Replacement:="", lookat:=xlPart
End With
End Sub


ist kurz, einfach verständlich, schnell und hätte gegenüber der String-Umformung den Vorteil, daß wenn mal ne Ausländische Nummer reinrutscht, diese erhalten bleibt.
Gruß, Daniel

Anzeige
AW: Teile von Zahlen auslesen und einfügen
10.08.2008 11:32:00
Zahlen
Hi,
stimmt schon, aber bei sowas '4914924949' verhagelt' s die Sache.
Gruß Gerd

AW: Teile von Zahlen auslesen und einfügen
10.08.2008 14:25:03
Zahlen
Hi
das funktioniert schon, allerdings unter folgenden Vorraussetzungen:
- das erste Hochkomma muss das erste Zeichen im String sein
- das erste Hochkomma muss auch wirklich zum String gehören und darf nicht das Texterkennungszeichen sein.
(dh. das Hochkomma ist immer sichtbar und nicht nur in der Bearbeitungszeile)
das Hochkomma als Text-Erkennungszeichen sollte man stehen lassen, da die Formatierung der Zelle als Text nicht besonders zuverklässig ist.
importiere mal diese Datei und lass mein Makro drüberlaufen, dann gehts korrekt, auch bei deiner SpezialZahl:
https://www.herber.de/bbs/user/54489.txt
Gruß, Daniel

Anzeige
AW: Teile von Zahlen auslesen und einfügen
10.08.2008 22:44:00
Zahlen
Hi,
das führende Hochkomma will der Fragesteller aber aus dem Zellentext eleminieren.
Gruß Gerd

AW: Teile von Zahlen auslesen und einfügen
10.08.2008 23:33:13
Zahlen
Hi
eigentlich ist das Hochkomma ja schon draussen.
es wird nicht angezeigt, es wird nicht ausgedruckt, beim Kopieren in die Zwischenablage wird es nicht übernommen, wenn du eine Abrfrage auf die Datei machst (=Links(..;1)) erscheint es nicht.
- das Hochkomma ist nicht Bestandteil des Zellwertes.
es ist lediglich die Kennzeichung, daß die Ziffernfolge als String interpretiert werden soll.
aus diesem Grund muss es auch in der Bearbeitungszeile erscheinen, sonst könnte man das ja nicht ändern.
Vorteil des Hochkommas ist, daß man bei der Verwendung nicht an das Zellformat TEXT gebunden ist.
aber der Fragesteller hat ja eine Lösung gefunden, von daher brauchen wir das hier nicht weiter zu diskutieren
Gruß, Daniel

Anzeige
VBA Beispiel
10.08.2008 12:09:36
Matthias
Hallo
Hier meine VBA-Variante

Option Explicit
Sub splitten()
Dim j As Long
Dim Strg1 As String, LoI As Long
For j = 1 To 10
For LoI = 1 To Len(Cells(j, 1))
Strg1 = Mid(Cells(j, 1), LoI, 1)
If IsNumeric(Strg1) Then
Cells(j, 2) = Cells(j, 2) & Strg1
End If
Next
'Überprüfen ob links "49" steht
If Left(Cells(j, 2), 2) = "49" Then
Cells(j, 3).Value = "0" & Right(Cells(j, 2), Len(Cells(j, 2)) - 2)
End If
Next j
End Sub




und so siehts dann aus:
Userbild
Userbild

Anzeige
AW: VBA Beispiel
10.08.2008 21:55:34
bernie
Hallo Matthias, hallo Leute,
zunächst einmal vielen Dank für Euere Bemühungen.
Ich habe mir das Makro von Matthias etwas abgewandelt und es funktioniert super.
Jetzt hätte ich noch eine Frage:
Das Marko erfüllt die Bedingung =if ... "49" Then....
Wie und wo gebe ich ein, wenn das Makro die z.B. die Tel.-Nr. 491727602212 nur dann in
01727602212 umgewandeln werden soll, wenn die Rufnummer länger als 7 Ziffern ist?
Grund hierfür: Ich möchte ausschließen, daß z.B. die Vorwahl 490 und Rufnummmer 46232 in 0046232 umgewandelt wird.
Vielleicht kann mir auch da jemand weiterhelfen.
bernie

Anzeige
mit ner äußeren Schleife
11.08.2008 10:00:00
Matthias
Hallo
setze eine äußere Schleife.

If Len(Cells(j, 2)) > 9 Then 'hier mußt Du Dich selbst entscheiden
'Überprüfen ob links "49" steht
    If Left(Cells(j, 2), 2) = "49" Then
Cells(j, 3).Value = "0" & Right(Cells(j, 2), Len(Cells(j, 2)) - 2)
End If
Else
Cells(j, 3).Value = Cells(j, 2).Value
End If


Gruß Matthias L

AW: mit ner äußeren Schleife
11.08.2008 12:53:00
Daniel
Hi
wieso äußere Schleife?
man muss doch einfach nur die IF-Abfrage um eine weiter Option erweitern:

If Left(Cells(j, 2), 2) = "49"  And Len(Cells(j,2)) > 7 Then


Gruß, Daniel

Anzeige
AW: mit ner äußeren Schleife
11.08.2008 15:44:47
bernie
Hallo Helfer,
vielen Dank für die Hilfe(n) und die sackundigen Vorschläge.
Das Makro funktioniert jetzt wie geschmiert :-))
Bernie

ja, Du hattest Recht, Danke ...
12.08.2008 00:01:00
Matthias
Hallo
Meine Antwort war einfach zu schnell und spontan.
Aber es freut mich und ich finde es gut, wenn jemand aufpasst :o)
Ich danke Dir auf jeden Fall für die Verbesserung.
Gruß Matthias

OT 2mal IF oder mit AND nur 1 IF
12.08.2008 07:19:51
Reinhard

Hallo Daniel, Matthias,
soweit ich zu wissen glaube ist der Codeansatz von Matthias schneller.
Vba prüft den kompletten AND-Ausdruck durch, auch wenn schon die erste Bedingung "falsch" ist,  _
es wertet also bei der AND-Methode "Left(Cells(j, 2), 2) = "49"" und "Len(Cells(j,2)) > 7" aus.
Okay, bemerkbar macht sich daß sicher erst bei tausenden von Zeilen.
Gruß
Reinhard


Anzeige
AW: OT 2mal IF oder mit AND nur 1 IF
12.08.2008 13:29:00
Daniel
Hi
das ist natürlich richtig.
allerdings hat mich die Formulierung "Schleife" etwas verwirrt, da ich "Schleife" mit FOR-NEXT oder DO-LOOP assoziiere, nicht aber mit IF THEN.
Gruß, Daniel

AW: OT 2mal IF oder mit AND nur 1 IF
12.08.2008 14:42:00
Reinhard
Hallo Daniel,
ich habe mal den worst case ausgetestet, also beide Auswertungen der Terme sind falsch, so wird mir angezeigt, daß die Variante ohne And, also mit 2 IFs doppelt so schnell ist, hätte ich absolut nicht vermutet, hätte einen kleineren Faktor geschätzt.
Bei XL2007 kann der Unterschied dann mit vielen Zellen die du abprüfen mußt so sein, je nach And oder nicht, kannste Kaffeepause machen, oder gleich ne lange Mittagspause :-))
Nachstehend mein Testcode, wundere dich bitte dich nicht warum ich den Test 2mal durchführe, mit unterschiedlichen Reihenfolgen der Call-Aufrufen.
Das hat mit der Problematik von hier null zu tun, es liegt daran, daß ich früher schon gelegentlich die Erfahrung machte, wenn man mittels "Timer"-Auswertung die unterschiedlichen Schnelligkeiten zweier prozeduren testen wollte, es einen Unterschied machte ob man
Call a
Call b
benutzte oder
Call b
Call a
Diese Erfahrung machte ich schon gelegentlich, und da ich das ja mehrmals durchlaufen ließ kann man m.E. ausschließen daß dafür die CPU-Auslatung schuld ist wo sich vielleicht grad in dem Moment Antivir updatet oder sowas.
Aber egal, Code den ich posten könnte damit das jmd auf seinem Rechner nachspielt habe ich nicht.
wenn ich wieder sowas habe, was sich reproduzieren läßt, mache ich eine Anfrage hier daraus.
Gruß
Reinhard

Option Explicit
Public Anz As Long
Sub Test()
Dim T As Single
Anz = 65536
Call Werte
T = Timer
Call CheckOhneAnd
Cells(1, 2) = Timer - T
T = Timer
Call CheckMitAnd
Cells(2, 2) = Timer - T
T = Timer
Call CheckMitAnd
Cells(2, 3) = Timer - T
T = Timer
Call CheckOhneAnd
Cells(1, 3) = Timer - T
End Sub
Sub CheckOhneAnd()
Dim Zei As Long
For Zei = 1 To Anz
If Cells(Zei, 1) > 1 Then
If Cells(Zei, 1) > 2 Then
End If
End If
Next Zei
End Sub
Sub CheckMitAnd()
Dim Zei As Long
For Zei = 1 To Anz
If Cells(Zei, 1) > 1 And Cells(Zei, 1) > 2 Then
End If
Next Zei
End Sub
Sub Werte()
Dim Zei As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Zei = 1 To Anz
Cells(Zei, 1) = 0
Next Zei
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


AW: OT 2mal IF oder mit AND nur 1 IF
12.08.2008 17:29:00
Daniel
Hi
ja, das ist klar, bei der AND-Version wird ja immer alles geprüft, dh wenn kein fall zutrifft ist sie viel langsamer, wenn alle Fälle zutreffen dürfte sie fast genaus schnell sein
sagen wir es mal so, wenn es um Geschwindigkeit geht, dann ist der hier gepostete Code sowieso nicht anzuraten.
wenn man Speed braucht, gibts 2 Varianten:
1. man überlegt sich ne Formellösung und schreibt dann nur ein Makro daß die Formel in eine Hilfsspalte schreibt und dann die Formelergebnisse in die eigentlsche Zelle kopiert
2. man kopiert die Werte vorab in ein Array, lässt dann das Makro über dieses Array laufen und schreibt zum Schluss die Werte in die Zellen zurück.
solange direkt mit Range-Objekten gearbeitet wird, brauchen wir und über Geschwindikgeit nicht unterhalten, die ist nicht vorhanden.

312 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige