Microsoft Excel

Herbers Excel/VBA-Archiv

Teile von Zahlen auslesen und einfügen

Betrifft: Teile von Zahlen auslesen und einfügen von: bernie
Geschrieben am: 08.08.2008 22:57:01

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

  

Betrifft: AW: Teile von Zahlen auslesen und einfügen von: Uduuh
Geschrieben am: 08.08.2008 22:59:59

Hallo,
warum ein .doc und nicht die Mappe mit Daten????

Gruß aus’m Pott
Udo



  

Betrifft: AW: Teile von Zahlen auslesen und einfügen von: Daniel
Geschrieben am: 08.08.2008 23:49:57

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)<8;A1;"'0" & teil(A1;4;länge(A1)-4))


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)<8;A1;"'0" & teil(A1;3;länge(A1)-3))


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


  

Betrifft: Peter ist ein Hase von: Uduuh
Geschrieben am: 09.08.2008 00:00:30

Hallo Daniel,
kein Hasen
Peter Haserodt.
;-)

Gruß aus’m Pott
Udo



  

Betrifft: AW: Teile von Zahlen auslesen und einfügen von: Matthias L
Geschrieben am: 09.08.2008 08:37:40

Hallo

Benutz doch Daten Text in Spalten



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


  

Betrifft: AW: Teile von Zahlen auslesen und einfügen von: bernie
Geschrieben am: 10.08.2008 08:40:01

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


  

Betrifft: AW: Teile von Zahlen auslesen und einfügen von: Daniel
Geschrieben am: 10.08.2008 10:57:20

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


  

Betrifft: AW: Teile von Zahlen auslesen und einfügen von: Gerd L
Geschrieben am: 10.08.2008 11:32:35

Hi,
stimmt schon, aber bei sowas '4914924949' verhagelt' s die Sache.
Gruß Gerd


  

Betrifft: AW: Teile von Zahlen auslesen und einfügen von: Daniel
Geschrieben am: 10.08.2008 14:25:03

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


  

Betrifft: AW: Teile von Zahlen auslesen und einfügen von: Gerd L
Geschrieben am: 10.08.2008 22:44:17

Hi,

das führende Hochkomma will der Fragesteller aber aus dem Zellentext eleminieren.

Gruß Gerd


  

Betrifft: AW: Teile von Zahlen auslesen und einfügen von: Daniel
Geschrieben am: 10.08.2008 23:33:13

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


  

Betrifft: VBA Beispiel von: Matthias L
Geschrieben am: 10.08.2008 12:09:36

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:







  

Betrifft: AW: VBA Beispiel von: bernie
Geschrieben am: 10.08.2008 21:55:34

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


  

Betrifft: mit ner äußeren Schleife von: Matthias L
Geschrieben am: 11.08.2008 10:00:24

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


  

Betrifft: AW: mit ner äußeren Schleife von: Daniel
Geschrieben am: 11.08.2008 12:53:54

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


  

Betrifft: AW: mit ner äußeren Schleife von: bernie
Geschrieben am: 11.08.2008 15:44:47

Hallo Helfer,

vielen Dank für die Hilfe(n) und die sackundigen Vorschläge.
Das Makro funktioniert jetzt wie geschmiert :-))

Bernie


  

Betrifft: ja, Du hattest Recht, Danke ... von: Matthias L
Geschrieben am: 12.08.2008 00:01:54

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


  

Betrifft: OT 2mal IF oder mit AND nur 1 IF von: Reinhard
Geschrieben am: 12.08.2008 07:19:51


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




  

Betrifft: AW: OT 2mal IF oder mit AND nur 1 IF von: Daniel
Geschrieben am: 12.08.2008 13:29:35

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


  

Betrifft: AW: OT 2mal IF oder mit AND nur 1 IF von: Reinhard
Geschrieben am: 12.08.2008 14:42:08

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




  

Betrifft: AW: OT 2mal IF oder mit AND nur 1 IF von: Daniel
Geschrieben am: 12.08.2008 17:29:53

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.


 

Beiträge aus den Excel-Beispielen zum Thema "Teile von Zahlen auslesen und einfügen"