Kim tiene una columna de datos que se usa para contener un código de ubicación. Este código consta de una sola letra seguida de dos dígitos, como A03 o B12.

A Kim le gustaría formatear condicionalmente la columna para que cualquier cosa ingresada en la columna que no use este patrón se resalte de alguna manera.

Hay muchas formas de abordar este problema. Cada enfoque depende del desarrollo de una fórmula que se pueda usar dentro de una regla de formato condicional para devolver Verdadero o Falso y activar el formato condicional. (Este consejo no explicará cómo crear una regla de formato condicional, sino que se centrará en las diversas fórmulas que se pueden usar en la regla. La forma de crear reglas de formato condicional se explica en otros ExcelTips.)

Cualquiera que sea la fórmula que se junte tiene que probar tres cosas:

  • Hay exactamente tres caracteres en la cadena.

  • El primer carácter es una letra.

  • El segundo y tercer caracteres son dígitos.

Averiguar si el texto de una celda tiene solo tres caracteres es bastante sencillo; puedes usar la función LEN para hacerlo:

=LEN(A1)=3

Averiguar si el primer carácter es una letra también es bastante fácil. De hecho, hay varias formas de hacerlo. Cualquiera de los siguientes devolverá True si el primer carácter es una letra:

=AND(CODE(LEFT(A1,1))>64,CODE(LEFT(A1,1))<91)

=AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z")

Estos verifican para asegurarse de que solo una letra mayúscula esté en la primera posición. Si también desea aceptar letras minúsculas, puede usar una variación de la segunda prueba:

=AND(UPPER(LEFT(A1,1))>="A",UPPER(LEFT(A1,1))<="Z")

Si tanto las letras mayúsculas como las minúsculas son aceptables (junto con prácticamente cualquier otro símbolo), entonces podría considerar usar la siguiente prueba:

=NOT(ISNUMBER(LEFT(A1,1)+0))

Aquí hay un par de formas en las que puede aplicar la tercera prueba, si el segundo y tercer caracteres son dígitos:

=ISNUMBER(VALUE(RIGHT(A1,2)))

=ISNUMBER(--RIGHT(A1,2))

Tenga en cuenta que estos enfoques tratan a los dos últimos personajes juntos. Esto significa que «1», «11» y «111» pasarían la prueba; se verifican correctamente como números. Si su fórmula estuviera verificando solo los dos últimos dígitos, esto podría ser un problema, pero el hecho de que también incluirá la primera verificación (para la longitud total de la cadena en la celda y que debe ser 3), entonces no No presenta un problema en absoluto.

El truco, ahora, es combinar el enfoque de su elección para cada una de las tres pruebas en una sola fórmula. Esto se puede hacer usando la función AND. Escogeré la más corta de cada una de las pruebas y las combinaré de esta manera:

=AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2)))

Tal como está escrito, esta fórmula devuelve Verdadero si se pasan todas las pruebas, lo que significa que la celda contiene un código de ubicación con un patrón válido. Esto funcionaría muy bien como formato condicional si Kim formatee la columna como un color (digamos, verde) y luego use el formato condicional para eliminar el color verde. Esto puede parecer al revés y es posible que solo desee aplicar un formato si no se cumple el patrón. Si ese es el caso, simplemente encierre la fórmula en una función NO para revertir el verdadero / falso que se devuelve:

=NOT(AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2))))

Como puede ver, usar una fórmula como esta puede ser un poco complicado. Si lo prefiere, puede crear una UDF (función definida por el usuario) que acortaría un poco la regla de formato condicional. La siguiente macro es un buen camino a seguir:

Function IsBadPattern(sCell As String) As Boolean     IsBadPattern = Not(sCell Like "[A-Z][0-9][0-9]")

End Function

Para usar la UDF en su regla de formato condicional, todo lo que necesita hacer es usar la siguiente fórmula:

=IsBadPattern(A1)

El resultado de la UDF será VERDADERO si la cadena en la celda referenciada no coincide con el patrón que deseaba. Tal como está escrito, no permitirá el uso de letras minúsculas en la posición del primer carácter. Si necesita permitir letras minúsculas, no necesita cambiar la UDF. En su lugar, cambie la fórmula por la siguiente:

=IsBadPattern(UPPER(A1))

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

Este consejo (9976) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365.