Cómo encontrar caracteres no válidos

Bienvenido a este tutorial sobre cómo encontrar caracteres no válidos en Microsoft Excel. En este tutorial, exploraremos diferentes métodos, incluidas fórmulas y formato condicional, para detectar y resaltar los caracteres que usted define como “no válidos” en sus hojas de cálculo de Excel. Dado que puede personalizar fácilmente la lista de caracteres “no válidos”, este enfoque es extremadamente flexible y puede usarse en muchas situaciones diferentes.

Video

paso a paso

Para ilustrar los pasos, creó tres ejercicios. Puede obtener el archivo usando el enlace a continuación si lo desea.

Ejercicio 1: uso de la función ENCONTRAR para buscar caracteres no válidos

Comenzamos definiendo una lista de caracteres no válidos y almacenándolos en una tabla. Con fines ilustrativos, mantendré la lista breve. La tabla se llama Tabla1:

Nota: si no ha creado una tabla antes, diríjase a Insertar Tabla. Puede nombrar la tabla si lo desea utilizando el campo Nombre de la tabla; De lo contrario, el nombre predeterminado es Tabla1.

Ahora tenemos un montón de celdas y queremos ver si alguna de ellas contiene alguno de los caracteres de la Tabla 1.

Una opción es escribir una fórmula en una columna adyacente que utilice la función ENCONTRAR. Por ejemplo, en la celda C15, podemos usar la siguiente fórmula:

=BUSCAR(Tabla1, B15)

Esta fórmula busca los caracteres de la Tabla1 en la celda B15.

Notarás que nuestra fórmula única devuelve dos resultados… uno para cada carácter en la Tabla1. Si la función ENCONTRAR devuelve un número, significa que el carácter se encuentra en esa posición número. Si FIND devuelve un error, no se encuentra el carácter.

Nota: FIND distingue entre mayúsculas y minúsculas y no admite comodines. Dependiendo de en qué esté trabajando, puede considerar usar BÚSQUEDA, que no distingue entre mayúsculas y minúsculas y admite comodinas.

Entonces, en este ejemplo, la tabla tiene dos caracteres, por lo que se devuelven dos resultados. El primer resultado es 13, lo que significa que el primer carácter “no válido” se encontró en la posición 13 en B15. ¡El segundo resultado es el #VALOR! error, lo que significa que no se encontró el segundo carácter de la lista.

Pero necesitamos una forma de colapsar todos estos resultados en una sola celda. Una opción sería utilizar la función CONTAR. Contará el número de celdas con un valor e ignorará los errores. Entonces, podemos actualizar nuestra fórmula de la siguiente manera:

=CONTAR(BUSCAR(Tabla1,B15))

Le damos Enter y bam:

Podemos completar esta fórmula actualizada para aplicarla a las celdas restantes en nuestra columna auxiliar:

Si el resultado es 0, no se encuentra ninguno de los caracteres “no válidos” (como se define). Si los resultados son 0, entonces se ha encontrado un carácter no válido.

Prima

Si desea convertir la columna de resultados en casillas de verificación usando el control de celda de casilla de verificación (como se explica en mi publicación anterior), puede convertir los resultados de la fórmula a valores booleanos usando un operador de comparación de la siguiente manera:

=CONTAR(BUSCAR(Tabla1,B15))0

Esto devuelve VERDADERO/FALSO en lugar del recuento. Luego puede seleccionar el rango de la fórmula y usar el comando Insertar Controles de celda Casilla de verificación y bam:

Nota: el comando Insertar Controles de celda Casilla de verificación NO está disponible en todas las versiones de Excel. Al momento de escribir este artículo, se está implementando para los usuarios de Excel 365.

Nota: si desea marcar las casillas que no tienen caracteres no válidos, actualice la fórmula a: COUNT(FIND(Table1,B15))=0

Ejercicio 2: Aplicar formato condicional

Digamos que nos gustaría aplicar formato condicional para resaltar las celdas que contienen caracteres no válidos.

Bueno, una opción sencilla sería formatear condicionalmente la columna auxiliar. Comenzamos seleccionando todas las celdas de fórmula en la columna auxiliar.

A continuación, vamos a Inicio Formato condicional Resaltar reglas de celda Mayor que. En el cuadro de diálogo resultante establecemos la condición en 0 y elegimos el formato deseado.

En lugar de formatear la columna auxiliar C, podríamos formatear la columna de datos B. Comience seleccionando el rango y luego Inicio Formato condicional Nueva regla Usar una fórmula para determinar qué celdas formatear. En el cuadro de diálogo resultante, establecemos la fórmula igual a la primera celda de la columna auxiliar usando una referencia de fila relativa de columna absoluta:

Haga clic en Aceptar para aplicar el formato condicional:

Pero, ¿y si quisiéramos eliminar la columna auxiliar? Bueno… eso nos lleva a nuestro ejercicio final.

Ejercicio 3: eliminación de la columna auxiliar

En este ejercicio, trasladaremos efectivamente la lógica de la fórmula de la columna auxiliar a la regla de formato condicional. De esa manera, eliminamos la necesidad de mostrar la columna auxiliar en el libro.

Primero, definiremos un nombre para la referencia de la tabla usando el Administrador de nombres. Fórmulas Administrador de nombres Nuevo. Asígnele un nombre (como “caracteres”) y, en el campo “Se refiere a”, ingrese el nombre de la tabla.

Haga clic en Aceptar para crear el nombre definido.

Seleccione las celdas a las que desea aplicar formato condicional y Inicio Formato condicional Nueva regla Usar una fórmula para determinar qué celdas formatear.

En el campo de fórmula, ingrese:

=CONTAR(BUSCAR(caracteres, B14))

Seleccione un formato y haga clic en Aceptar para aplicar el formato condicional.

Como puede ver, básicamente trasladamos nuestra fórmula auxiliar a la regla de formato condicional, eliminando así la necesidad de mostrarla en la hoja de trabajo.

Conclusión

¡Felicidades! Ha aprendido a encontrar caracteres no válidos en Excel mediante fórmulas y formato condicional. Si sigue los pasos de este tutorial, podrá identificar y abordar fácilmente cualquier carácter no válido en sus datos. Comience a explorar y utilizar estas técnicas para garantizar la precisión e integridad de sus hojas de cálculo de Excel.

Si tiene alguna alternativa, sugerencia o pregunta, compártala publicando un comentario a continuación… ¡gracias!

archivo de muestra

Buscar caracteres no válidos.xlsx Descargar

PREGUNTAS MÁS FRECUENTES:

P: ¿Qué es un carácter no válido en Excel?

R: Para los propósitos de esta publicación, definimos un conjunto de caracteres que queremos encontrar. No son inválidos en el sentido de que Excel no los permita. No son válidos porque los definimos como tales y el enfoque presentado en esta publicación hace que sea muy fácil personalizar la lista de caracteres “no válidos” como se desee.

P: ¿Puedo encontrar caracteres no válidos sólo en una columna específica?

R: Sí, puede aplicar la función BUSCAR y el formato condicional a una columna específica seleccionando el rango de celdas y ajustando las fórmulas en consecuencia.

P: ¿Cómo puedo eliminar el formato condicional si ya no es necesario?

R: Para eliminar el formato condicional, seleccione las celdas formateadas, vaya a la pestaña Inicio, haga clic en Formato condicional y elija “Borrar reglas”.

P: ¿Puedo utilizar comodines para buscar caracteres no válidos?

R: Sí, puede utilizar la función BUSCAR, que admite caracteres comodín, en lugar de la función BUSCAR, que no los admite. Tenga en cuenta que la función BUSCAR distingue entre mayúsculas y minúsculas y la función BUSCAR no.