Die Power von Excel nutzen und anwenden

Klick den Tipp!


Bilderauswahl

Excel - Diagrammerstellung


exceldiagramme

Microsoft Award 2011

Microsoft Community Contributor

Nicht druckbare Zeichen entfernen Drucken
Fragen und Antworten zu Excel - Makro- und VBA-Lösungen
Geschrieben von: Frank Arendt-Theilen   

 

Nach dem Import einer TXT- oder CSV-Datei sind immer wieder nicht lesbare Zeichen in den Zellen enthalten. Die Funktion Suchen & Ersetzen hilft mir nicht weiter. Wie werde ich diese Zeichen los?

Excel hat die integrierte Funktion SÄUBERN(). Diese Funktion entfernt alle nicht druckbaren Zeichen des 7-Bit-ASCII-Codes (Steuerzeichen mit dem Wert zwischen 0 und 31).

Enthält z.B. die Zelle A1 eine Textkonstante mit nicht druckbaren Zeichen, lautet die Formel:

=SÄUBERN(A1)


Diese Funktion steht auch in VBA als WorksheetFunction CLEAN() zur Verfügung.

Allerdings bleiben dabei einige nicht druckbare Zeichen des Unicodes erhalten. Dies sind die Zeichen mit dem Code 127, 129, 141, 143, 144 und 157.

Damit zum einen die Textkonstanten direkt bearbeitet und zum anderen alle nicht druckbaren Zeichen entfernt werden, soll ein Makro die Arbeit erledigen.

Kopieren Sie das folgende Makro in ein Standardmodul der persönlichen Makroarbeitsmappe. Damit steht es für alle geöffneten Arbeitsmappen zur Verfügung.


Sub AlleNichtDruckbarenZeichenEntfernen()
Dim rngZelle As Range

  For Each rngZelle In ActiveCell.SpecialCells(xlCellTypeConstants, 23)

    rngZelle.Value = Application.WorksheetFunction.Clean(rngZelle.Value)

    'Unicodes 127, 129, 141, 143, 144 und 157 entfernen
    If rngZelle.Value Like "*" & Chr(127) & "*" Then
      rngZelle.Value = Replace(rngZelle.Value, Chr(127), "", 1, , vbBinaryCompare)
    End If

    If rngZelle.Value Like "*" & Chr(129) & "*" Then
      rngZelle.Value = Replace(rngZelle.Value, Chr(129), "", 1, , vbBinaryCompare)
    End If

    If rngZelle.Value Like "*" & Chr(141) & "*" Then
      rngZelle.Value = Replace(rngZelle.Value, Chr(141), "", 1, , vbBinaryCompare)
    End If

    If rngZelle.Value Like "*" & Chr(143) & "*" Then
      rngZelle.Value = Replace(rngZelle.Value, Chr(143), "", 1, , vbBinaryCompare)
    End If

    If rngZelle.Value Like "*" & Chr(144) & "*" Then
      rngZelle.Value = Replace(rngZelle.Value, Chr(144), "", 1, , vbBinaryCompare)
    End If

    If rngZelle.Value Like "*" & Chr(157) & "*" Then
      rngZelle.Value = Replace(rngZelle.Value, Chr(157), "", 1, , vbBinaryCompare)
    End If

  Next

End Sub


Erläuterungen zum Makro:

Das Makro führt eine For Each ... Next-Schleife über alle Zellen im aktuellen Tabellenblatt aus, die einen konstanten Wert enthalten und entfernt die nicht druckbaren Zeichen. Formelzellen bleiben unberühert.

Zunächst entfernt die Anweisung

rngZelle.Value = Application.WorksheetFunction.Clean(rngZelle.Value)

alle nicht druckbaren Zeichen des 7-Bit-ASCII-Codes. Anschließend wird mit der If-Anweisung nach jedem noch eventuell vorhandenem, nicht druckbaren Zeichen aus dem Unicode  127, 129, 141, 143, 144 und 157 gesucht und bei Vorhandensein mit der Replace-Funktion gegen Nichts ("") ausgetauscht.

Der Like-Operator vereinfacht durch die Verwendung des * (Sternchen) als Ersatzzeichen für mehrere Zeichen das Durchsuchen der jeweiligen Konstanten.


If rngZelle.Value Like "*" & Chr(127) & "*" Then
rngZelle.Value = Replace(rngZelle.Value, Chr(127), "", 1, , vbBinaryCompare)
End If


Umgangssprachlich bedeuten diese Anweisungen:
Befinden sich irgendwo in der Konstanten ein Zeichen mit dem Code 127, dann tausche es gegen Nichts ("") aus.

 

Schlüsselwörter:
nicht druckbar, Steuerzeichen, entfernen, CLEAN(), SÄUBERN(), Replace, Like, SpecialCells, 7-Bit-ASCII-Code, Unicode
Zuletzt aktualisiert am Samstag, den 28. April 2012 um 00:38 Uhr
 

Kommentare 

 
# 2012-04-27 12:42
Hallo auch hier ;-)

Lässt sich Säubern vielleicht zusammen mit WECHSELN nutzen? Als zu wechselndes Zeichen würde ich dann ZEICHEN(127) etc gegen "" eintragen. Nur eine Idee und noch nicht getestet.

Vielleicht mal bis zu den Anwender- oder Trainertagen
Andreas Thehos
Antworten
 
 
# 2012-04-28 00:26
Hallo Andreas,
genau das erledigt das obige Makro, ansonsten müsste ich eine sechsfach verschachtelte WECHSELN()-Formel aufbauen.

Mit freundlichem Gruß aus der Rattenfängersta dt Hameln

Frank Arendt-Theilen
Antworten
 

Heute

Montag, 21. Mai 2012

Video2Brain-Autor

v2bautor

Office2010-Blog


Office2010-Blog Logo

Hinweis

Fast alle Hardware- und Software- bezeichnungen, die auf dieser Web- site erwähnt werden, sind gleichzeitig eingetragene Warenzeichen und sollten als solche behandelt werden.