Microsoft Excel

Herbers Excel/VBA-Archiv

führende Null bei Text

Betrifft: führende Null bei Text von: Petra
Geschrieben am: 12.08.2008 14:49:26

Schönen Tag allerseits.
ich habe eine Spalte mit Text. Dieser Text besteht entweder nur aus Ziffern oder Ziffern/Buchstaben.
Bei vielen Zellen beginnt der Text mit einer oder mehreren Nullen (0). Diese führenden (Text)Nullen sollen nun durch "Blanks" ersetzt werden, sodaß die ursprüngliche Textlänge erhalten bleibt. Wie könnte man das "Problem" per VBA in den Griff kriegen?
Liebe Grüße Petra

  

Betrifft: AW: führende Null bei Text von: Rudi Maintaire
Geschrieben am: 12.08.2008 15:13:40

Hallo,
können auch mitten im Text Nullen sein?

Gruß
Rudi


  

Betrifft: AW: führende Null bei Text von: Petra
Geschrieben am: 12.08.2008 15:33:25

Hallo Rudi
Ja, aber die müssen erhalten bleiben, also nur die führenden!!
Grüße Petra


  

Betrifft: AW: führende Null bei Text von: Petra
Geschrieben am: 12.08.2008 15:36:36

Hi Rudi
vielleicht eine Erleichterung, alle Texte sind/müssen 6-stellig sein.
Gruß Petra


  

Betrifft: AW: führende Null bei Text von: Rudi Maintaire
Geschrieben am: 12.08.2008 15:49:41

Hallo,

Function NullToBlank(ByVal sText As String)
  Dim i As Integer
  i = 1
  Do While Asc(Mid(sText, i, 1)) = 48
    Mid(sText, i, 1) = " "
    i = i + 1
  Loop
  NullToBlank = sText
End Function



Sub tt()
  Dim rngC As Range
  For Each rngC In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    rngC = NullToBlank(rngC)
  Next
End Sub


Gruß
Rudi


  

Betrifft: AW: führende Null bei Text von: Petra
Geschrieben am: 12.08.2008 15:58:51

Hi Rudi
Klappt, DANKE
Lieben Gruß Petra


  

Betrifft: AW: führende Null bei Text von: Reinhard
Geschrieben am: 12.08.2008 15:34:37

Hi Petra,

probier mal dieses:

Sub tt()
Dim Zei As Long, Pos As Integer
For Zei = 1 To Range("A" & Rows.Count).End(xlUp).Row
   Pos = 1
   While Mid(Cells(Zei, 1), Pos, 1) = "0"
      Mid(Cells(Zei, 1), Pos, 1) = " "
      Pos = Pos + 1
   Wend
Next Zei
End Sub


Gruß
reinhard


  

Betrifft: AW: führende Null bei Text von: Petra
Geschrieben am: 12.08.2008 15:43:51

Hi Reinhard
Makro mozt cells an!(Variable erforderlich)
Trotzdem Danke
Gruß Petra


  

Betrifft: AW: führende Null bei Text von: Daniel
Geschrieben am: 12.08.2008 16:05:43

Hi

das liegt daran, daß man mit der MID-Funktion nur lesen, aber nicht Werte in einem String vereändern kann.
dazu braucht man die Replace-Funktion.

probier mal diesen Code, damit sollte es funktionieren:

Sub tt()
 Dim Zei As Long, Pos As Integer
 For Zei = 1 To Range("A" & Rows.Count).End(xlUp).Row
    Pos = 1
    While Mid(Cells(Zei, 1), Pos, 1) = "0"
       Cells(Zei, 1) = WorksheetFunction.Replace(Cells(Zei, 1), Pos, 1, " ")
       Pos = Pos + 1
    Wend
 Next Zei
 End
End Sub



Gruß, Daniel


  

Betrifft: AW: führende Null bei Text von: Rudi Maintaire
Geschrieben am: 12.08.2008 16:22:42

Hallo,

das liegt daran, daß man mit der MID-Funktion nur lesen, aber nicht Werte in einem String vereändern kann.


Und warum funzt dann meine Function?

Gruß
Rudi


  

Betrifft: AW: führende Null bei Text von: Daniel
Geschrieben am: 12.08.2008 16:37:25

Hi

weil du die MID-ANWEISUNG auf eine STRING-Variable anwendest,
und Reinhard auf ein RANGE-Objekt.

der erste fall geht, der 2. Fall funktioniert nicht, von daher muss ich meine Aussage etwas korrigieren:

die MID-Anweisung zum Verändern von Textteilen funktioniert nicht bei Range-Objekten.

Gruß, Daniel


  

Betrifft: AW: führende Null bei Text von: Petra
Geschrieben am: 12.08.2008 16:25:11

Hi Daniel
dein Code ist zu radikal, er löscht auch Nullen in der Mitte und am Ende, so wird z.B. aus 000100 die 1
Trotzdem Danke
Gruß Petra


  

Betrifft: AW: führende Null bei Text von: Daniel
Geschrieben am: 12.08.2008 17:01:51

Hi

das liegt an der automatischen Typumwandlung von Excel, die bei Eingabe in Zellen aus Textstrings Zahlen macht, sobald dies möglich ist, dann verändert sich der Zellinhalt (die Blanks am Anfang verschwinden, der Inhalt wird kürzer und die POS-Variable zeigt auf eine Falsche Stelle.

sobald sich ein Buchstabe in dem Wert befindet, läuft das Makro einwandfrei.

folgende Abhilfen sind möglich:
Variante 1.
die Spalte wird vorab mit dem Zahlenformat "Text" formatiert. Hier der Code dazu:

Sub tt1()
 Dim Zei As Long, Pos As Integer
 Columns(1).NumberFormat = "@"
 For Zei = 1 To Range("A" & Rows.Count).End(xlUp).Row
    Pos = 1
    While Mid(Cells(Zei, 1), Pos, 1) = "0"
       Cells(Zei, 1) = "'" & WorksheetFunction.Replace(Cells(Zei, 1), Pos, 1, " ")
       Pos = Pos + 1
    Wend
 Next Zei
 End
End Sub




Variante 2.
beim Zurückschreiben des Zellwerts legen wir fest, daß keine Typumwandlung stattfinden soll.
(das Hochkomma gehört nicht zum Zellinhalt, es ist für Excel nur das Zeichen, diesen Text auf keinen Fall in eine Zahl oder Formel umzuwandeln)

Sub tt2()
 Dim Zei As Long, Pos As Integer
 For Zei = 1 To Range("A" & Rows.Count).End(xlUp).Row
    Pos = 1
    While Mid(Cells(Zei, 1), Pos, 1) = "0"
       Cells(Zei, 1) = "'" & WorksheetFunction.Replace(Cells(Zei, 1), Pos, 1, " ")
       Pos = Pos + 1
    Wend
 Next Zei
 End
End Sub




Gruß, Daniel