Translate

Samstag, 22. Februar 2014

MS Excel: Umwandeln von Text in Zahlen per VBA


Wenn Sie in Excel eine Zahl eingeben, können Sie diese auch als Text definieren. Damit Excel aber korrekte Berechnungen vornehmen kann, müssen Zahlen entsprechend als solche deklariert sein. Dies erreichen Sie, in dem Sie den Zellen das Zahlenformat Standard oder Zahl vergeben.

Dies klappt jedoch nicht immer, somit können keine Berechnungen mit diesen Zellen durchgeführt werden, obwohl diese das Zahlenformat Standard zugewiesen wurden.

Z.B. nach dem Import von Daten aus anderen Quellen passiert es häufigen, dass Texte, die aus Ziffern bestehen, wie Zahlen erscheinen aber Excel erkennt sie nicht als Zahl, sondern als Text. 

Eine Möglichkeiten diese wieder in effektive Zahlen umzuwandeln ist das verwenden der integrierten Fehlerüberprüfung (insofern aktiviert).

Gehen Sie dazu wie folgt vor:
1.   Markieren Sie die zu konvertierenden Zellen
2.   Klicken Sie auf das Ausrufezeichen rechts von der Zelle, insofern es erscheint ;-)
3.   Wählen Sie nun den Unterpunkt
"In eine Zahl umwandeln"

 

Leider wird die Konvertierung mit dem Ausrufezeichen oft nicht von Excel angeboten, wo dann andere Lösungswege angewandt werden müssen, wie z.B. alle Zellen mit 1 zu multiplizieren.
Diese Lösungsansätze sind jedoch umständlich und kosten Zeit, vor allem dann, wenn dieser Vorgang sehr häufig gemacht werden muss.
Was liegt also näher als uns dafür eine kleine VBA-Routine zu schreiben, die genau diese Anforderung dauerhaft und zuverlässig löst:

Public Sub TextcellToNumber(Optional TargetRange As Range _
                            , Optional NumberFormat As String = "General")
    Dim rg As Range, tCursor As Long, tStatusBar As String
   
    On Error GoTo ExitProc
    tCursor = Application.Cursor
    Application.Cursor = xlWait
    If Not Application.StatusBar = False Then tStatusBar = Application.StatusBar
    Application.StatusBar = "Please wait until the conversion is completed..."
    Application.ScreenUpdating = False
   
    If TargetRange Is Nothing Then Set TargetRange = Application.Selection
    If TargetRange.CountLarge = 1 Then
        If IsEmpty(TargetRange) Or TargetRange.HasFormula _
           Or Not (TargetRange.Formula = TargetRange) Then GoTo ExitProc
    Else
        Set TargetRange = TargetRange.SpecialCells(xlCellTypeConstants, xlTextValues)
    End If
    On Error GoTo NextRange
    For Each rg In TargetRange
        If IsDate(rg.Value) Then
            rg.NumberFormat = "m/d/yyyy"
            rg = CDate(rg.Value)
            rg.NumberFormat = NumberFormat
        ElseIf IsNumeric(rg.Value) Then
            rg.NumberFormat = NumberFormat
            rg = CDbl(rg.Value)
        End If
NextRange:
    Next

ExitProc:
    Application.StatusBar = tStatusBar
    Application.ScreenUpdating = True
    Application.Cursor = tCursor
End Sub

Nun zur Anwendung dieser Prozedur 
Um in VBA z.B. alle Zellen von der Spalte A der Tabelle1 in Zahlen zu konvertieren, rufen Sie die Prozedur in VBA wie folgt auf:
TextcellToNumber Tabelle1.Range("A:A")
Möchten Sie dabei gleich ein besonderes Zahlenformat (z.B. Tausendertrennzeichen mit 2 Dezimalstellen) umgesetzt haben, dann lautet der Befehl so:
TextcellToNumber Tabelle1.Range("A:A"), "#,##.00"
Möchten Sie alle aktuell markierten Zellen konvertiert haben, dann lautet der Befehl ganz einfach so:
TextcellToNumber Application.Selection

Selbstverständliche läßt sich die Prozedur auch aus dem Applikationsfenster von Excel aufrufen, in dem Sie das Makro auf ein Symbol in der Symbolleiste für den Schnellzugriff legen.
Die Vorgehensweise dazu ist hier erklärt.

Achtung! Damit die Prozedur auch dauerhaft in Ihrem Excel verfügbar ist, sollten Sie die Prozedur in einem Modul in der Datei Personal.xlsb (Version bis 2003 Personl.xls) hinterlegen.

Und noch eine kleine letzte Hürde:
Prozeduren mit Paramater erscheinen leider nicht in der Makro-Befehlsauswahliste.
Dazu tricksen wir das System etwas aus, in dem wir die Parameter kurzfristig entfernen:
  1. Ändern Sie den Kopf der Prozedur wie folgt:
    Public Sub TextcellToNumber()
  2. Nun können Sie das Makro in die Schnellzugriffsleiste hinzufügen.
  3. Anschließend wiederherstellen Sie den Prozedurkopf wie gehabt.
Jetzt sind Sie in der Lage mit einem Mausklick den aktuellen markierten Bereich von Text auf Zahl zu konvertieren.

Sollte Ihnen das alles zu aufwendig sein, dann installieren Sie doch einfach den Shortcut Menu Manager für Microsoft® Excel hier.
Die Funktion steht Ihnen dann direkt auf der Rechten Maustaste zur Verfügung:

Keine Kommentare:

Kommentar veröffentlichen