Steve tiene una hoja de trabajo que contiene más de diez mil filas, y cada celda de la columna A contiene un nombre de archivo. Estos nombres deben seguir dos reglas, y Steve necesita descubrir qué nombres violan cualquiera de las reglas. Si un nombre de archivo contiene un guión, también debe tener un solo espacio antes y después del guión. La segunda regla es que si el nombre contiene una coma, no debe haber ningún espacio antes, sino un solo espacio después.

Steve se pregunta cómo puede resaltar las celdas que violan (o ambas)

de estas reglas.

Cada vez que alguien menciona que quiere «resaltar» algo en una hoja de trabajo, la mayoría de la gente piensa en usar formato condicional. Esta instancia no es una excepción; podría utilizar fácilmente el formato condicional para resaltar las infracciones de patrones. La clave para desarrollar la regla de formato condicional es crear una fórmula que devuelva Verdadero si se infringe el patrón. Esta fórmula comprueba ambas infracciones:

=OR(ISNUMBER(FIND("-",SUBSTITUTE(A1," - ",""))), ISNUMBER(FIND(",",SUBSTITUTE(A1,", ",""))), ISNUMBER(FIND(" ,",A1)))

He dividido la fórmula en tres líneas aquí, pero debería considerarse una fórmula completa. La fórmula elimina los patrones correctos (espacio, guión, espacio y coma, espacio) del nombre del archivo y luego verifica si queda un guión o una coma en el nombre del archivo. Si queda uno, la fórmula devuelve Verdadero.

Puede configurar una regla de formato condicional para usar la fórmula de esta manera:

  1. Seleccione las celdas que contienen todos los nombres de archivo que desea verificar.

  2. Con la pestaña Inicio de la cinta mostrada, haga clic en la opción Formato condicional en el grupo Estilos. Excel muestra una paleta de opciones relacionadas con el formato condicional.

  3. Elija Resaltar reglas de celdas y luego elija Más reglas en el submenú resultante. Excel muestra el cuadro de diálogo Nueva regla de formato.

(Ver figura 1)

  1. En el área Seleccionar un tipo de regla en la parte superior del cuadro de diálogo, elija Usar una fórmula para determinar qué celdas formatear.

  2. En el cuadro Formato de valores donde esta fórmula es verdadera, ingrese la fórmula larga ya discutida.

  3. Haga clic en Formato para mostrar el cuadro de diálogo Formato de celdas.

  4. Con los controles del cuadro de diálogo, especifique un formato que desee utilizar para resaltar las celdas que violan su patrón.

  5. Haga clic en Aceptar para cerrar el cuadro de diálogo Formato de celdas. El formato que especificó en el paso 7 debería aparecer ahora en el área de vista previa de la regla.

  6. Haga clic en Aceptar.

Si las celdas que seleccionó en el paso 1 no comenzaron con la celda A1, deberá modificar la fórmula utilizada en el paso 5 para reflejar su celda inicial. (Las tres instancias de A1 en la fórmula deberían cambiarse para hacer referencia a su celda inicial).

Hay dos grandes «errores» al utilizar esta fórmula en su regla de formato condicional. Primero, no detecta espacios dobles. Entonces, por ejemplo, si el nombre del archivo contiene «espacio, espacio, guión, espacio», eso sería una violación del patrón. Sin embargo, la función SUSTITUIR en la fórmula eliminaría el «espacio, guión, espacio», dejando el espacio adicional en la cadena resultante. Este único espacio no se detectaría como una violación del patrón, aunque lo sea.

La solución a esto sería una fórmula mucho más larga o evitar la ruta de formato condicional por completo y comenzar a usar columnas auxiliares. Esto se alimenta directamente en el segundo «te pillé», y es uno grande:

Si aplica formato condicional (o agrega columnas auxiliares que contienen fórmulas) a diez mil filas, notará un marcado aumento en el tiempo que lleva recalcular su hoja de trabajo. No hay forma de evitar esto cuando comienza a agregar tantas fórmulas a la hoja de trabajo.

Por esta razón, puede que le resulte más apropiado desarrollar una macro que resalte las celdas. Luego, la macro podría ejecutarse manualmente cuando desee verificar los patrones, lo que significa que el recálculo normal de su hoja de trabajo no se ralentiza.

La siguiente macro está diseñada para ejecutarse en un rango de celdas seleccionado.

Comprueba que no haya dos espacios antes de un guión, dos espacios después de un guión, un espacio antes de una coma o dos espacios después de una coma. Luego, elimina los guiones y las comas con patrones correctos del nombre de archivo y comprueba si quedan guiones o comas. Si se observa una infracción de cualquiera de estas condiciones, la celda se formatea en amarillo.

Sub CheckFilenames1()

Dim bBad As Boolean     Dim c As Range     Dim sTemp1 As String     Dim sTemp2 As String

For Each c In Selection         bBad = False         sTemp1 = c.Text

If Instr(sTemp1, "  -") > 0 Then bBad = True         If Instr(sTemp1, "-  ") > 0 Then bBad = True         If Instr(sTemp1, " ,") > 0 Then bBad = True         If Instr(sTemp1, ",  ") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, " - ", "")

If Instr(sTemp2, "-") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, ", ", "")

If Instr(sTemp2, ",") > 0 Then bBad = True

If bBad Then             c.Interior.Color = vbYellow         Else             c.Interior.Color = xlColorIndexNone         End If     Next c End Sub

La macro puede tardar un poco en ejecutarse pero, nuevamente, solo necesita ejecutarse cuando desee verificar los nombres de campo. Si no desea que la macro «estropee» el formato de la celda, entonces puede querer una versión que inserte texto en la columna a la derecha de cualquier nombre de archivo que viole el patrón deseado.

Sub CheckFilenames2()

Dim bBad As Boolean     Dim c As Range     Dim sTemp1 As String     Dim sTemp2 As String

For Each c In Selection         bBad = False         sTemp1 = c.Text

If InStr(sTemp1, "  -") > 0 Then bBad = True         If InStr(sTemp1, "-  ") > 0 Then bBad = True         If InStr(sTemp1, " ,") > 0 Then bBad = True         If InStr(sTemp1, ",  ") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, " - ", "")

If InStr(sTemp2, "-") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, ", ", "")

If InStr(sTemp2, ",") > 0 Then bBad = True

If bBad Then c.Offset(0, 1) = "BAD"

Next c End Sub

Cuando se ejecuta, esta variación de la macro inserta el texto «MALO» en la celda a la derecha de los nombres de archivo con patrones incorrectos. Luego, puede usar las capacidades de filtrado de Excel para mostrar solo las filas que contienen el texto.

Por supuesto, es posible que desee llevar todo esto un paso más allá y permitir que la macro modifique cualquier nombre de archivo con formato incorrecto. La siguiente macro hace su trabajo en las celdas que haya seleccionado. Asegura que cada guión esté rodeado por un solo espacio y que cada coma sea seguida solo por un solo espacio.

Sub FixFilenames()

Dim myArry() As String     Dim sTemp As String     Dim c As Range     Dim s As Variant

For Each c In Selection         myArry = Split(c, "-")

sTemp = ""

For Each s In myArry             If sTemp > "" Then                 sTemp = sTemp & " - " & Trim(s)

Else                 sTemp = Trim(s)

End If         Next s         myArry = Split(sTemp, ",")

sTemp = ""

For Each s In myArry             If sTemp > "" Then                 sTemp = sTemp & ", " & Trim(s)

Else                 sTemp = Trim(s)

End If         Next s         c = sTemp     Next c End Sub

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (3015) se aplica a Microsoft Excel 2007, 2010, 2013 y 2016.