Bestaat er een Excel formule om speciale tekens in een cel te identificeren?

Geen code? Maar het is zo kort en makkelijk en mooi en… :(

Je RegEx patroon [^A-Za-z0-9_-] wordt gebruikt om alle speciale tekens in alle cellen te verwijderen.

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

Dit is zo dicht als ik kan komen bij je oorspronkelijke vraag.

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

De tweede code is een door de gebruiker gedefinieerde functie =RegExCheck(A1,"[^A-Za-z0-9_-]") met 2 argumenten. Het eerste is de cel die moet worden gecontroleerd. Het tweede is het RegEx patroon om op te controleren. Indien het patroon overeenkomt met een van de karakters in uw cel, zal het 1 teruggeven, anders 0.

U kunt het gebruiken zoals elke andere normale Excel formule als u eerst de VBA editor opent met ALT+F11, een nieuwe module (!) invoegt en de onderstaande code plakt.

[] 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)

• *

Voor gebruikers die nieuw zijn met RegEx zal ik het patroon uitleggen: [^A-Za-z0-9_-]

Met iets soortgelijks als nixda’s code, is hier een door de gebruiker gedefinieerde functie die 1 teruggeeft als de cel speciale tekens bevat.

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

User Defined Functions (UDF’s) zijn zeer eenvoudig te installeren en te gebruiken:

1. ALT-F11 brengt het VBE-venster op
2. ALT-I ALT-M opent een nieuwe module
3. plak het spul erin en sluit het VBE venster

Als je de werkmap opslaat, wordt de UDF mee opgeslagen. Als u een versie van Excel gebruikt die later is dan 2003, moet u het bestand opslaan als .xlsm in plaats van .xlsx

Om de UDF te verwijderen:

1. roep het VBE-venster op zoals hierboven
2. wis de code
3. sluit het VBE-venster

Om de UDF uit Excel te gebruiken:

=IsSpecial(A1)

Voor meer informatie over macro’s in het algemeen, zie: http://www.mvps.org/dmcritchie/excel/getstarted.htm

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

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

voor specifieke informatie over UDF’s

Macro’s moeten ingeschakeld zijn om dit te laten werken!

Hier is een voorwaardelijke opmaak oplossing die de records met speciale tekens zal markeren.

Pas gewoon een nieuwe voorwaardelijke opmaakregel op uw gegevens toe die de onderstaande (extreem lange) formule gebruikt, waarbij A1 het eerste record in de kolom met bestandsnamen is:

=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)

Deze formule controleert elk karakter van elke bestandsnaam en bepaalt of de ASCII code buiten de toegestane karakterwaarden valt. Helaas zijn de toegestane tekencodes niet allemaal aaneengesloten, dus daarom moet de formule sommen van SUMPRODUCTs gebruiken. De formule retourneert het aantal slechte karakters dat er is. Alle cellen die een waarde groter dan 0 teruggeven worden gemarkeerd.

Voorbeeld:

Ik gebruikte een andere aanpak om speciale tekens te vinden. Ik maakte nieuwe kolommen voor elk van de toegestane tekens, en gebruikte dan een formule als deze om te tellen hoe vaak dat toegestane teken in elke rijingang (Z2) voorkwam:

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

Vervolgens telde ik het aantal toegestane tekens in elke rij bij elkaar op, en vergeleek dat met de totale lengte van de rijingang.

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

En tenslotte sorteerde ik op de laatste kolom (BF2) om negatieve waarden te vinden, die mij leidden naar de kolommen die correctie behoefden.