2013-10-16 13:34:39 +0000 2013-10-16 13:34:39 +0000
13
13

Gibt es eine Excel-Formel, um Sonderzeichen in einer Zelle zu identifizieren?

Wir haben etwa 3500 Dokumente, deren Dateinamen manuell gesäubert werden müssen, um Sonderzeichen wie Klammern, Doppelpunkte, Semikolons, Kommas usw. zu entfernen.

Ich habe eine Textdatei, die ich in Excel kopiert habe, und ich versuche, eine Spalte zu erstellen, die den Dateinamen zur Änderung markiert, wenn er Sonderzeichen enthält. Die Pseudocode-Formel wäre 

=IF (cellname contains [^a-zA-z_-0-9], then "1", else "0")

, um die Zeile zu markieren, wenn sie andere Zeichen als A-Z, 0-9, - oder _ enthält, unabhängig von der Groß-/Kleinschreibung.

Kennt jemand etwas, das für mich funktionieren könnte? Ich zögere, eine massive if-Anweisung zu codieren, wenn es etwas Schnelles und Einfaches gibt.
Quelle

Antworten (4)

19
19
19
2013-10-16 14:26:04 +0000

Kein Code? Aber es ist so kurz und einfach und schön und… :(

Ihr RegEx-Muster [^A-Za-z0-9_-] wird verwendet, um alle Sonderzeichen in allen Zellen zu entfernen. 

Sub RegExReplace()

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True

    RegEx.Pattern = "[^A-Za-z0-9_-]"
    For Each objCell In ActiveSheet.UsedRange.Cells
        objCell.Value = RegEx.Replace(objCell.Value, "")
    Next

End Sub

Edit

Das ist so nah, wie ich Ihrer ursprünglichen Frage kommen kann. 

Function RegExCheck(objCell As Range, strPattern As String)

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = strPattern

    If RegEx.Replace(objCell.Value, "") = objCell.Value Then
        RegExCheck = 0
    Else
        RegExCheck = 1
    End If

End Function

Der zweite Code ist eine benutzerdefinierte Funktion =RegExCheck(A1,"[^A-Za-z0-9_-]") mit 2 Argumenten. Das erste ist die zu prüfende Zelle. Das zweite ist das RegEx-Muster, auf das geprüft werden soll. Wenn das Muster mit einem der Zeichen in der Zelle übereinstimmt, wird 1 zurückgegeben, andernfalls 0.

Sie können diese Funktion wie jede andere normale Excel-Formel verwenden, wenn Sie zuerst den VBA-Editor mit ALT+F11 öffnen, ein neues Modul (!) einfügen und den folgenden Code einfügen. 

[] stands for a group of expressions
^ is a logical NOT
[^] Combine them to get a group of signs which should not be included
A-Z matches every character from A to Z (upper case)
a-z matches every character from a to z (lower case)
0-9 matches every digit
_ matches a _
- matches a - (This sign breaks your pattern if it's at the wrong position)

Für Benutzer, die neu in RegEx sind, erkläre ich das Muster: [^A-Za-z0-9_-]

Quelle
7
7
7
2013-10-16 15:31:19 +0000

Unter Verwendung von etwas Ähnlichem wie Nixdas Code, ist hier eine benutzerdefinierte Funktion, die 1 zurückgibt, wenn die Zelle Sonderzeichen enthält. 

Public Function IsSpecial(s As String) As Long
    Dim L As Long, LL As Long
    Dim sCh As String
    IsSpecial = 0
    For L = 1 To Len(s)
        sCh = Mid(s, L, 1)
        If sCh Like "[0-9a-zA-Z]" Or sCh = "_" Then
        Else
            IsSpecial = 1
            Exit Function
        End If
    Next L
End Function

Benutzerdefinierte Funktionen (UDFs) sind sehr einfach zu installieren und zu verwenden:

  1. ALT-F11 ruft das VBE-Fenster auf
  2. ALT-I ALT-M öffnet ein neues Modul
  3. Fügen Sie die Sachen ein und schließen Sie das VBE-Fenster

Wenn Sie die Arbeitsmappe speichern, wird die UDF mit ihr gespeichert. Wenn Sie eine Version von Excel später als 2003 verwenden, müssen Sie die Datei als .xlsm und nicht als .xlsx speichern

Um die UDF zu entfernen:

  1. rufen Sie das VBE-Fenster wie oben auf
  2. löschen Sie den Code
  3. schließen Sie das VBE-Fenster

Um die UDF aus Excel zu verwenden:

=IsSpecial(A1)

Um mehr über Makros im Allgemeinen zu erfahren, siehe: http://www.mvps.org/dmcritchie/excel/getstarted.htm

und http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

und http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

für Einzelheiten zu UDFs

Makros müssen aktiviert sein, damit dies funktioniert!
Quelle
2
2
2
2013-10-16 21:05:57 +0000

Hier ist eine Lösung für die bedingte Formatierung, die die Datensätze mit Sonderzeichen kennzeichnet.

Wenden Sie einfach eine neue bedingte Formatierungsregel auf Ihre Daten an, die die folgende (extrem lange) Formel verwendet, wobei A1 der erste Datensatz in der Spalte mit den Dateinamen ist: 

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<48)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>45))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>57)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<65))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>90)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<97)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>95))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>122)*1)

Diese Formel prüft jedes Zeichen jedes Dateinamens und bestimmt, ob sein ASCII-Code außerhalb der zulässigen Zeichenwerte liegt. Leider sind die zulässigen Zeichencodes nicht alle zusammenhängend, deshalb muss die Formel Summen von SUMPRODUCTs verwenden. Die Formel gibt die Anzahl der fehlerhaften Zeichen zurück, die es gibt. Alle Zellen, die einen Wert größer als 0 zurückgeben, werden markiert.

Beispiel:

Quelle
1
1
1
2016-06-20 21:36:00 +0000

Ich habe einen anderen Ansatz verwendet, um Sonderzeichen zu finden. Ich erstellte neue Spalten für jedes der erlaubten Zeichen und verwendete dann eine Formel wie diese, um zu zählen, wie oft das erlaubte Zeichen in jedem Zeileneintrag (Z2) vorkam: 

AA2=LEN($Z2)-LEN(SUBSTITUTE($Z2,AA$1,""))
AB2=LEN($Z2)-LEN(SUBSTITUTE($Z2,AB$1,""))
...

Dann summierte ich die Anzahl der erlaubten Zeichen in jeder Zeile und verglich sie dann mit der Gesamtlänge des Zeileneintrags. 

BE2=LEN(Z2)
BF2=SUM(AA2:BC2)-BE2

Und schließlich habe ich nach der letzten Spalte (BF2) sortiert, um negative Werte zu finden, was mich zu den Spalten führte, die korrigiert werden mussten.
Quelle