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

¿Existe una fórmula de Excel para identificar caracteres especiales en una celda?

Tenemos unos 3500 documentos cuyos nombres de archivo deben ser depurados manualmente para eliminar caracteres especiales como corchetes, dos puntos, punto y coma, comas, etc.

Tengo un archivo de texto que he volcado en Excel, y estoy intentando crear una columna que marque el nombre del archivo para modificarlo si incluye caracteres especiales. La fórmula del pseudocódigo sería 

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

para marcar la fila si contiene cualquier carácter que no sea A-Z, 0-9, - o _, independientemente de las mayúsculas y minúsculas.

¿Alguien sabe de algo que me pueda servir? No me atrevo a codificar y masificar la sentencia if si hay algo rápido y sencillo.
Fuente

Respuestas (4)

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

¿No hay código? Pero es tan corto y fácil y bonito y… :(

Su patrón RegEx [^A-Za-z0-9_-] se utiliza para eliminar todos los caracteres especiales en todas las celdas. 

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

Esto es lo más parecido a tu pregunta original. 

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

El segundo código es una función definida por el usuario =RegExCheck(A1,"[^A-Za-z0-9_-]") con 2 argumentos. El primero es la celda a comprobar. El segundo es el patrón RegEx a comprobar. Si el patrón coincide con alguno de los caracteres de la celda, devolverá 1, de lo contrario 0.

Puedes utilizarla como cualquier otra fórmula normal de Excel si primero abres el editor VBA con ALT+F11, insertas un nuevo módulo (!) y pegas el código de abajo. 

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

Para los usuarios nuevos en RegEx explicaré su patrón: [^A-Za-z0-9_-]

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

Usando algo similar al código de nixda, aquí hay una función definida por el usuario que devolverá 1 si la celda tiene caracteres especiales. 

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

Las Funciones Definidas por el Usuario (UDFs) son muy fáciles de instalar y utilizar:

  1. ALT-F11 hace aparecer la ventana VBE
  2. ALT-I ALT-M abre una ventana de ALT-I ALT-M abre un nuevo módulo
  3. Pegue el material y cierre la ventana VBE

Si guarda el libro, la UDF se guardará con él. Si utiliza una versión de Excel posterior a la 2003, debe guardar el archivo como .xlsm en lugar de .xlsx

Para eliminar el UDF:

  1. abra la ventana VBE como se indica arriba
  2. borre el código
  3. cierre la ventana VBE

Para utilizar el UDF desde Excel:

=IsSpecial(A1)

Para saber más sobre las macros en general, ver: ¡ http://www.mvps.org/dmcritchie/excel/getstarted.htm

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

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

para conocer los detalles de las UDFs

Las macros deben estar activadas para que esto funcione!
Fuente
2
2
2
2013-10-16 21:05:57 +0000

He aquí una solución de formato condicional que marcará los registros con caracteres especiales.

Sólo tiene que aplicar una nueva regla de formato condicional a sus datos que utilice la fórmula (extremadamente larga) que aparece a continuación, donde A1 es el primer registro de la columna de nombres de archivo: 

=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 comprueba cada carácter de cada nombre de archivo y determina si su código ASCII está fuera de los valores de caracteres permitidos. Por desgracia, los códigos de caracteres permitidos no son todos contiguos, por lo que la fórmula tiene que utilizar sumas de SUMPRODUCTs. La fórmula devuelve el número de caracteres malos que hay. Las celdas que devuelven un valor mayor que 0 se marcan.

Ejemplo:

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

Utilicé un enfoque diferente para encontrar los caracteres especiales. Creé nuevas columnas para cada uno de los caracteres permitidos, y luego usé una fórmula como ésta para contar cuántas veces ese carácter permitido estaba en cada entrada de la fila (Z2): 

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

Luego sumé el número de caracteres permitidos en cada fila, y luego lo comparé con la longitud total de la entrada de la fila. 

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

Y finalmente, ordené la última columna (BF2) para encontrar valores negativos, lo que me llevó a las columnas que necesitaban corrección.
Fuente