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

Esiste una formula di Excel per identificare i caratteri speciali in una cella?

Abbiamo circa 3500 documenti i cui nomi di file devono essere puliti manualmente per rimuovere caratteri speciali come parentesi, punti, punti e virgola, virgole, ecc.

Ho un file di testo che ho scaricato in Excel, e sto cercando di creare una colonna che segnali il nome del file da modificare se include caratteri speciali. La formula pseudocodice sarebbe 

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

per segnalare la riga se contiene qualsiasi carattere diverso da A-Z, 0-9, - o _, indipendentemente dal caso.

Qualcuno conosce qualcosa che possa funzionare per me? Sono riluttante a codificare e a codificare la massiccia dichiarazione if se c'è qualcosa di semplice e veloce.
Fonte

Risposte (4)

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

Nessun codice? Ma è così breve e facile e bello e… :(

Il tuo schema RegEx [^A-Za-z0-9_-] è usato per rimuovere tutti i caratteri speciali in tutte le celle. 

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

Questo è il più vicino che posso ottenere alla tua domanda originale. 

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

Il secondo codice è una funzione definita dall'utente =RegExCheck(A1,"[^A-Za-z0-9_-]") con 2 argomenti. Il primo è la cella da controllare. Il secondo è il modello RegEx da controllare. Se il pattern corrisponde a uno qualsiasi dei caratteri nella cella, restituirà 1 altrimenti 0.

Potete usarla come qualsiasi altra formula normale di Excel se prima aprite l'editor VBA con ALT+F11, inserite un nuovo modulo (!) e incollate il codice sottostante. 

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

Per gli utenti nuovi di RegEx spiego il tuo schema: [^A-Za-z0-9_-]

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

Usando qualcosa di simile al codice di nixda, ecco una funzione definita dall'utente che restituirà 1 se la cella ha caratteri speciali. 

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

Le funzioni definite dall'utente (UDF) sono molto facili da installare e utilizzare:

  1. ALT-F11 fa apparire la finestra VBE
  2. ALT-I ALT-M apre un nuovo modulo
  3. incollare la roba e chiudere la finestra VBE

Se salvate la cartella di lavoro, la UDF verrà salvata con essa. Se state usando una versione di Excel successiva alla 2003, dovete salvare il file come .xlsm piuttosto che come .xlsx

Per rimuovere la UDF:

  1. richiamate la finestra VBE come sopra
  2. cancellate il codice
  3. chiudete la finestra VBE

Per usare la UDF da Excel:

=IsSpecial(A1)

Per saperne di più sulle macro in generale, vedere: http://www.mvps.org/dmcritchie/excel/getstarted.htm

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

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

per specifiche sulle UDF

Le macro devono essere abilitate per funzionare!
Fonte
2
2
2
2013-10-16 21:05:57 +0000

Ecco una soluzione di formattazione condizionale che segnalerà i record con caratteri speciali.

Basta applicare una nuova regola di formattazione condizionale ai vostri dati che usa la formula (estremamente lunga) qui sotto, dove A1 è il primo record nella colonna dei nomi dei file: 

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

Questa formula controlla ogni carattere di ogni nome di file e determina se il suo codice ASCII è fuori dai valori di carattere consentiti. Sfortunatamente, i codici dei caratteri ammissibili non sono tutti contigui, ecco perché la formula deve usare somme di SUMPRODUCT. La formula restituisce il numero di caratteri sbagliati che ci sono. Tutte le celle che restituiscono un valore maggiore di 0 sono contrassegnate.

Esempio:

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

Ho usato un approccio diverso per trovare i caratteri speciali. Ho creato nuove colonne per ogni carattere permesso, e poi ho usato una formula come questa per contare quante volte quel carattere permesso era in ogni riga (Z2): 

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

Poi ho sommato il numero di caratteri permessi in ogni riga, e l'ho confrontato con la lunghezza totale della riga. 

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

E infine, ho ordinato sull'ultima colonna (BF2) per trovare valori negativi, che mi hanno portato alle colonne che avevano bisogno di correzione.
Fonte