Im Beitrag “Zahlenwerte richtig erkennen” habe ich Ihnen Lösungswege gezeigt, wenn Sie mit Zahlen nicht rechnen konnten, weil sie nicht als Zahl formatiert waren.
Teil 1: Zahlenwerte richtig erkennen
Teil 2: Zahlenwerte im Format Text speichern
Im heutigen Beitrag wollen wir genau das Gegenteil erreichen. Zahlen sollen als Text gespeichert werden. Sie werden sich vielleicht fragen, was dadurch erreicht werden soll. Ganz einfach, Excel erkennt keine vorangestellten Nullen. Wollen Sie z.B. eine Liste mit Kundennummern, Mitgliedsdaten oder ähnlichem führen, die mit einer oder mehrerer Nullen beginnen und mit denen keine Rechenoperationen durchgeführt geführt werden sollen, so bietet sich an, die Zahlen als Text zu formatieren. Sie können alternativ auch die Formatierung “Sonderformat” verwenden und z.B. die Anzahl der Stellen dort festlegen. Wenn Sie so festlegen, dass immer eine neunstellige Nummer angezeigt werden soll, werden bei kürzeren Zahlen die fehlenden Stellen vorne mit einer Null angezeigt. Allerdings ist das die reine Ansicht in der Tabelle. Im Eingabefeld wird die Zahl ohne die vorangestellten Nullen angezeigt. Dadurch werden Sie Probleme haben, wenn Sie unterschiedlich formatierte Listen z.B. über einen Sverweis miteinander abgleichen wollen.
Um nun also zu erreichen, dass die Zahlen als Text formatiert werden, können Sie vor der Eingabe der Zahlen den Zellbereich als Textfelder formatieren. Geben Sie nun Werte in die Felder ein, werden die Zeichen immer als Text formatiert. Bei Ziffern werden dann auch vorangestellte Nullen nicht nur optisch, sondern tatsächlich in der Zelle gespeichert.
Ein weiterer großer Vorteil von als Text gespeicherten Zahlen ist, dass Sie über einen Filter in der Überschrift problemlos benutzerdefiniert suchen können. So ist es zum Beispiel möglich über “entspricht” oder “beginnt mit” in der Liste zu suchen. Einen Postleitzahlenbereich können Sie bei “entspricht” suchen indem Sie den feststehenden beginnenden Zahlenbereich mit ein dem Stern als Platzhalter am Ende ergänzen. So werden Ihnen über die Eingabe von 80* z.B. alle Postleitzahlen angezeigt die mit den Ziffern 80 beginnen. Die weiteren drei Stellen sind variabel.
Einen größeren Bereich nachträglich als Text formatieren können Sie mit den beiden folgenden Makro-Varianten. Das Makro fügen Sie zuvor im VBA Editor in ein Modul ein.
In Variante 1 ist zunächst der Zellbereich auszuwählen den Sie formatieren wollen. Anschließend starten Sie das Makro.
Sub ZahlenwerteAlsTextFormatieren()
Dim Zelle As Range
Dim Speicher As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Selection.NumberFormat = "@"
For Each Zelle In Selection
Speicher = Zelle.Value
Zelle.Value = Speicher
Next Zelle
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Call Calculate
End Sub
In Variante 2 wird die Auswahl der Zellen vom Makro übernommen. Im folgenden Beispiel wird der Zellbereich A1 bis A10 ausgewählt und formatiert.
Sub ZahlenwerteAlsTextFormatieren()
Dim Zelle As Range
Dim Speicher As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Das Beispiel formatiert die Zellen A1 bis A10 ins Text-Format!
Range("A1:A10").Select
Selection.NumberFormat = "@"
For Each Zelle In Selection
Speicher = Zelle.Value
Zelle.Value = Speicher
Next Zelle
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Call Calculate
End Sub
Geht nicht!
strResult = Get_Characters_Until_First_Space(s1)
ws.Range(“I” & 1).NumberFormat = “@”
ws.Cells(1, “I”).Value = strResult
@Will Smith: Ich weiß nicht, was mir der Kommentar sagen soll. Der Code ist nicht von mir und auch nicht vollständig. Das Bruchstück kann ohne weiteren Code auch gar nicht funktionieren.
Der Original VBA-Code im Beitrag funktioniert. Allerdings natürlich nur, wenn auch der Zellbereich zuvor ausgewählt wurde. Natürlich kann man statt Selection auch einen Zellbereich angeben, so dass die vorherige Auswahl nicht notwendig ist.
Beim nächsten mal vielleicht nicht einfach nur den Code kopieren, sondern auch die Erklärung im Text lesen. Der veröffentlichte Code funktioniert jedenfalls, wenn man sich an die Anleitung hält.
Cells(xx, xx).FormulaR1C1 = “‘” & Cells(xx, xx).Value
@Mia Smith: Was soll das werden?
Die erste Zeile behält noch die vorangestellten Nullen, für die folgenden werden diese bei mir entfernt.? Gefährlich:)
@CT: Wurden die Zellen formatiert oder der VBA-Code genutzt?
Beim Formatieren der Zellen als Textfeld, sollte man möglichst immer die gesamte Spalte als Textfeld formatieren. Sonst kann es bei nachträglich eingefügten Werten, z.B. durch nachträglich unterhalb der Tabelle eingefügten Datensätzen tatsächlich dazu kommen, dass vorangestellte Nullen nicht bestehen bleiben, weil die Zelle noch im Format Standard formatiert ist. Es ist außerdem darauf zu achten, dass eingefügte Datensätze aus anderen Exceltabellen etc. ihr ursprüngliches Format behalten.
Beim VBA-Code werden nur die Zellen ins Textformat konvertiert, die Teil der Selektion waren. Wenn Sie sicher gehen wollen, dass alle Felder ersetzt werden, kann als Beispiel Selection z.B. durch Range(“C:C”) ersetzt werden, wenn die Werte in Spalte C stehen. Der VBA-Code muss natürlich je nach Einsatzzweck modifiziert werden. Es ist nur ein einfaches Beispiel!