|
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
|
|
|
|
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: [ 446 ]
|
|
|