Czy istnieje formuła programu Excel do identyfikacji znaków specjalnych w komórce?

Mamy około 3500 dokumentów, których nazwy plików należy ręcznie wyczyścić, aby usunąć znaki specjalne, takie jak nawiasy kwadratowe, średniki, średniki, przecinki itp.

Mam plik tekstowy, który zrzuciłem do programu Excel i próbuję utworzyć kolumnę, która oflaguje nazwę pliku do modyfikacji, jeśli zawiera znaki specjalne. Formuła pseudokodu będzie

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

aby oflagować wiersz, jeśli zawiera on znaki inne niż AZ, 0–9, - lub _, niezależnie od wielkości liter.

Czy ktoś wie o czymś, co może dla mnie zadziałać? Waham się przed kodem i masowym ifstwierdzeniem, jeśli jest coś szybkiego i łatwego.

Brak kodu? Ale jest tak krótki, łatwy i piękny i ... :(

Twój wzór RegEx [^A-Za-z0-9_-]służy do usuwania wszystkich znaków specjalnych we wszystkich komórkach.

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

Edytować

To jest tak blisko, jak tylko mogę dostać się do twojego pierwotnego pytania.

Drugi kod to funkcja zdefiniowana przez użytkownika =RegExCheck(A1,"[^A-Za-z0-9_-]")z 2 argumentami. Pierwszy to komórka do sprawdzenia. Drugi to wzorzec RegEx do sprawdzenia. Jeśli wzór pasuje do dowolnego znaku w komórce, zwróci 1 w przeciwnym razie 0.

Możesz użyć go jak każdej innej normalnej formuły Excela, jeśli najpierw otworzysz edytor VBA za pomocą ALT+ F11, wstawisz nowy moduł (!) I wklej poniższy kod.

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

Dla nowych użytkowników RegEx wyjaśnię Twój wzór: [^A-Za-z0-9_-]

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

Używając czegoś podobnego do kodu nixda, tutaj jest funkcja zdefiniowana przez użytkownika, która zwróci 1, jeśli komórka ma znaki specjalne.

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

Funkcje zdefiniowane przez użytkownika (UDF) są bardzo łatwe w instalacji i obsłudze:

1. ALT-F11 wyświetla okno VBE
2. ALT-I ALT-M otwiera nowy moduł
3. wklej rzeczy i zamknij okno VBE

Jeśli zapiszesz skoroszyt, UDF zostanie z nim zapisany. Jeśli używasz wersji programu Excel później niż 2003, musisz zapisać plik jako .xlsm zamiast .xlsx

Aby usunąć UDF:

1. wywołać okno VBE jak wyżej
2. wyczyść kod
3. zamknij okno VBE

Aby użyć UDF z Excela:

= IsSpecial (A1)

Aby dowiedzieć się więcej o makrach, zobacz:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

i

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

i

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

dla szczegółów na temat UDF

Makra muszą być włączone, aby to działało !

Oto rozwiązanie formatowania warunkowego, które oznaczy rekordy znakami specjalnymi.

Po prostu zastosuj nową regułę formatowania warunkowego do swoich danych, która używa (wyjątkowo długiej) formuły poniżej, gdzie A1jest pierwszy rekord w kolumnie nazw plików:

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

Ta formuła sprawdza każdy znak w nazwie pliku i określa, czy jego kod ASCII jest poza dopuszczalnymi wartościami znaków. Niestety, dopuszczalne kody znaków nie są ciągłe, dlatego formuła musi używać sumSUMPRODUCT s. Formuła zwraca liczbę złych znaków. Wszelkie komórki, które zwracają wartość większą niż 0, są oflagowane.

Przykład:

Użyłem innego podejścia do znalezienia znaków specjalnych. Utworzyłem nowe kolumny dla każdego z dozwolonych znaków, a następnie użyłem podobnej formuły, aby policzyć, ile razy dozwolony znak był w każdym wierszu (Z2):

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

Następnie zsumowałem liczbę dozwolonych znaków w każdym rzędzie, a następnie porównałem ją z całkowitą długością wpisu w wierszu.

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

Na koniec posortowałem ostatnią kolumnę (BF2), aby znaleźć wartości ujemne, które doprowadziły mnie do kolumn, które wymagały korekty.