Existe uma fórmula do Excel para identificar caracteres especiais em uma célula?

Question

Temos cerca de 3500 documentos cujos nomes de arquivo precisam ser removidos manualmente para remover caracteres especiais, como colchetes, ponto e vírgula, vírgula, etc.

Eu tenho um arquivo de texto que saltei no excel e estou tentando criar uma coluna que sinalize o nome do arquivo para modificação, se ele incluir caracteres especiais. A fórmula do pseudocódigo seria

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

para sinalizar a linha se contiver qualquer caractere diferente de A-Z, 0-9, - ou _, independentemente do caso.

Alguém sabe de algo que pode funcionar para mim? Estou hesitante em codificar e declaração if massiva se houver algo rápido e fácil.

    

Answer 1

Sem código? Mas é tão curto e fácil e lindo e ...: (

Seu padrão de RegEx [^A-Za-z0-9_-] é usado para remover todos os caracteres especiais em todas as células.

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

Editar

Isso é o mais perto que posso chegar da sua pergunta original.

Osegundocódigoéumafunçãodefinidapelousuário=RegExCheck(A1,"[^A-Za-z0-9_-]") com 2 argumentos. O primeiro é a célula a verificar. O segundo é o padrão RegEx para verificar. Se o padrão corresponder a qualquer um dos caracteres em sua célula, ele retornará 1, caso contrário, 0.

Você pode usá-lo como qualquer outra fórmula normal do Excel se primeiro abrir o editor do VBA com ALT + F11 , inserir um novo módulo ( !) e cole o código abaixo.

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

Para usuários novos no RegEx, explicarei seu padrão: [^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)

    

Answer 2

Usando algo semelhante ao código da nixda, aqui está uma função definida pelo usuário que retornará 1 se a célula tiver caracteres especiais.

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

Funções Definidas pelo Usuário (UDFs) são muito fáceis de instalar e usar:

1. ALT-F11 exibe a janela do VBE
2. ALT-I ALT-M abre um novo módulo
3. cole o material e feche a janela do VBE

Se você salvar a pasta de trabalho, a UDF será salva com ela. Se você estiver usando uma versão do Excel posterior a 2003, deverá salvar o arquivo como .xlsm em vez de .xlsx

Para remover o UDF:

1. abrir a janela do VBE como acima
2. limpe o código
3. feche a janela do VBE

Para usar o UDF do Excel:

= IsSpecial (A1)

Para saber mais sobre macros em geral, consulte:

link

e

link

e

link

para detalhes sobre UDFs

As macros devem estar ativadas para que isso funcione !

    

Answer 3

Aqui está uma solução de formatação condicional que sinaliza os registros com caracteres especiais.

Basta aplicar uma nova regra de formatação condicional aos dados que usam a fórmula (extremamente longa) abaixo, em que A1 é o primeiro registro na coluna de nomes de arquivos:

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

Esta fórmula verifica cada caractere de cada nome de arquivo e determina se seu código ASCII está fora dos valores de caractere permitidos. Infelizmente, os códigos de caracteres permitidos não são todos contíguos, por isso a fórmula tem que usar somas de SUMPRODUCT s. A fórmula retorna o número de caracteres ruins que existem. Todas as células que retornam um valor maior que 0 são marcadas.

Exemplo:

    

Answer 4

Eu usei uma abordagem diferente para encontrar caracteres especiais. Eu criei novas colunas para cada um dos caracteres permitidos e, em seguida, usei uma fórmula como essa para contar quantas vezes o caractere permitido estava em cada entrada de linha (Z2):

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

Em seguida, somei o número de caracteres permitidos em cada linha e comparei-o ao comprimento total da entrada da linha.

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

Por fim, classifiquei a última coluna (BF2) para encontrar valores negativos, o que me levou às colunas que precisavam de correção.