Tipp 0474 Prefix aus Zellen löschen
Autor/Einsender:
Datum:
  Alexander Fross
25.11.2005
Entwicklungsumgebung:   Excel 2000
Der Apostroph (Prefix) hat in Excel eine Sonderstellung. Wird das Prefix als 1. Zeichen in der Zelle verwendet, wird dieses nicht ausgegeben, sondern es formatiert den Inhalt der Zelle in das Textformat (NumberFormat = @). Einige Schnittstellen zu Excel wenden genau diese Vorgehensweise an. Das sieht z. B. so aus, dass alle leeren Zellen ein Prefix beinhalten sowie alle alphanumerischen Werte. Das führt jedoch ggf. dazu, dass die Weiterverarbeitung der Daten mit Formeln zu unerwünschten Resultaten führen kann. Dies wiederum müsste mit zusätzlichen WENN-Formeln abgefangen werden und macht die gesamte Formel unübersichtlich.
Grundsätzlich kann das Prefix der Zelle mit
 
    RangeObjekt.PrefixCharacter
 
ausgelesen und auch gelöscht werden (siehe Excel-Hilfe). Wenn nun jedes einzelne Prefix gelöscht werden soll, müsste man in jeder einzelnen Zelle die Eigenschaft PrefixCharacter mit "" zurücksetzen. Bei einer größeren Tabelle kann das dauern. Mit untenstehendem Code ist es möglich, das Prefix aus allen Zellen in einer Tabelle in kurzer Zeit zu löschen.
Es wird der gesamte Zellinhalt des benutzten Zellbereichs in ein Variant-Array geschrieben. Bei diesem Vorgang wird das Prefix nicht ausgelesen, d. h., im Array stehen die Zellinhalte ohne das Prefix. Wenn nun die Daten des Arrays zurück in die Tabelle geschrieben werden, geht das Prefix in den einzelnen Zellen verloren.
Damit die einzelnen Werte im korrekten Format in die Zellen geschrieben werden, wird der Datentyp Variant entsprechend angepasst. Diese Anpassung ist nicht immer zwingend notwendig. Es ist u. a. abhängig davon, wie die Tabelle formatiert ist, auf welche Weise die Daten in die Tabelle geschrieben wurden und letztendlich welche unterschiedlichen Datentypen die Tabelle aufweist.
In folgendem Beispiel werden die ersten zwei Spalten als Text (Datentyp String) formatiert, die übrigen ggf. vorhandenen Spalten in eine Zahl (hier Datentyp Double).
 
Option Explicit

Private Sub Delete_Prefix()
  Const cFORMAT_TEXT    As String = "@"
  Const cFORMAT_DOUBLE  As String = "#,##0.00;-#,##0.00;0.00;@"

  Dim objWks     As Worksheet
  Dim vntData    As Variant
  Dim nCol       As Long
  Dim nRow       As Long

  Application.ScreenUpdating = False

  For Each objWks In ThisWorkbook.Worksheets
    If Application.WorksheetFunction.CountA(objWks.Cells) > 0 Then
      With objWks.UsedRange
        vntData = .Formula
        For nCol = 1 To 2
          .Columns(nCol).NumberFormat = cFORMAT_TEXT
          For nRow = 1 To UBound(vntData, 1)
            vntData(nRow, nCol) = CStr(vntData(nRow, nCol))
          Next nRow
        Next nCol

        For nCol = 3 To UBound(vntData, 2)
          .Columns(nCol).NumberFormat = cFORMAT_DOUBLE
          For nRow = 1 To UBound(vntData, 1)
            Select Case True
              Case vntData(nRow, nCol) = ""
                vntData(nRow, nCol) = CDbl(0)
              Case IsNumeric(vntData(nRow, nCol))
                vntData(nRow, nCol) = CDbl(vntData(nRow, nCol))
            End Select
          Next nRow
        Next nCol

        .Formula = vntData
      End With
    End If
  Next objWks

  Application.ScreenUpdating = True
End Sub
 
Hinweis
Die im Download befindliche *.bas-Datei kann in Excel im VB-Editor importiert werden.

Windows-Version
95
98
ME
NT
2000
XP
Vista
Win 7
Excel-Version
95
97
2000
2002 (XP)
2003
2007
2010


Download  (2,5 kB) Downloads bisher: [ 372 ]

Vorheriger Tipp Zum Seitenanfang Nächster Tipp

Startseite | Projekte | Tutorials | API-Referenz | VB-/VBA-Tipps | Komponenten | Bücherecke | VB/VBA-Forum | VB.Net-Forum | DirectX-Forum | Foren-Archiv | DirectX | VB.Net-Tipps | Chat | Spielplatz | Links | Suchen | Stichwortverzeichnis | Feedback | Impressum

Seite empfehlen Bug-Report
Letzte Aktualisierung: Freitag, 26. August 2011