El tutorial muestra cómo validar datos en Excel usando expresiones regulares usando una función RegexMatch personalizada.
Cuando se trata de restringir la entrada del usuario en las hojas de cálculo de Excel, la validación de datos es indispensable. ¿Desea permitir solo números o fechas en una celda específica? ¿O limitar los valores de texto a una cierta longitud? ¿O tal vez prohibir tiempos fuera de un rango determinado? No hay problema, todo esto se puede hacer fácilmente con criterios de validación predefinidos o personalizados. Pero, ¿qué pasa si solo quiero permitir direcciones de correo electrónico válidas o cadenas de caracteres que coincidan con un patrón determinado? Desafortunadamente, eso no es posible. Regex dices? Hmm… ¡podría funcionar!
Cómo validar datos de Excel con Regex
Desafortunadamente, ninguna de las funciones integradas de Excel admite expresiones regulares y la validación de datos no es una excepción. Para poder validar la entrada de la celda usando expresiones regulares, primero debe crear una función Regex personalizada. Otra complicación es que las funciones definidas por el usuario de VBA no se pueden servir directamente para la validación de datos; necesitará un mediador en forma de fórmula con nombre.
Con lo anterior en mente, describamos brevemente los pasos a seguir para validar datos en Excel usando expresiones regulares:
- Cree una función Regex personalizada que verifique si un valor de entrada coincide con una expresión regular.
- Defina un nombre para su fórmula Regex.
- Configure una regla de validación de datos basada en la fórmula nombrada.
- Copie la configuración de validación en tantas celdas como desee.
¿Suena a plan? ¡Intentemos implementarlo en la práctica!
Valide datos de Excel usando expresiones regulares personalizadas
Este ejemplo aborda un caso muy común: cómo permitir solo valores de un patrón específico.
Supongamos que mantiene algunos SKU en su hoja de trabajo y desea asegurarse de que solo se incluyan aquellos que coincidan con un modelo en particular. Siempre que cada SKU consta de 2 grupos de caracteres separados por un guión, el primer grupo incluye 3 letras mayúsculas y el segundo grupo – 3 dígitos, puede identificar dichos valores utilizando la expresión regular a continuación.
Modelo: ^[A-Z]{3}-d{3}$
Tenga en cuenta que el comienzo (^) y el final ($) de la cadena están anclados, por lo que no se pueden ingresar caracteres en una celda que no sean los del patrón.
1. Agregue una función personalizada Regex Match
Comience insertando la función RegExpMatch en su libro de trabajo. El código ya está escrito por nuestros gurús de Excel, así que simplemente cópielo de la página anterior y péguelo en el editor de VBA.
Aquí está la sintaxis de la función como referencia:
RegExpMatch(texto, patrón, [match_case])
Dónde:
- Texto (requerido) – una cadena de origen (en nuestro contexto – una celda validada).
- Modelo (requerido) – una expresión regular para hacer coincidir.
- Match_case (opcional) – tipo de coincidencia. VERDADERO o falso: distingue entre mayúsculas y minúsculas; FALSO: no distingue entre mayúsculas y minúsculas.
Consejo. Si es usuario de Ultimate Suite, puede realizar la validación de datos Regex en Excel sin agregar ningún código VBA a sus libros de trabajo. Simplemente use una función AblebitsRegexMatch personalizada incluida con nuestras herramientas Regex.
2. Crea una fórmula con nombre
En la hoja de trabajo de destino, seleccione la celda A1 (independientemente de su contenido y de la celda que realmente validará), presione Ctrl+F3 para abrir el Administrador de nombres y definir un nombre para esta fórmula:
=RegExpMatch(Sheet1!A1, "^[A-Z]{3}-d{3}$")
O puede ingresar la expresión regular en una celda (A2 en este ejemplo) y proporcionar $A$2 al segundo argumento:
=RegExpMatch(Sheet1!A1, Sheet1!$A$2)
Para que la fórmula funcione correctamente, asegúrese de usar una referencia relativa para texto argumento (A1) y referencia absoluta para modelo ($2 dólares australianos).
Dado que nuestra fórmula está destinada a validar números de SKU, la nombramos en consecuencia: Validar_SKU.
¡Nota IMPORTANTE! Cuando defina la fórmula, compruebe si el primer argumento se refiere a la celda actualmente seleccionada, de lo contrario la fórmula no funcionará. Por ejemplo, si se selecciona la celda A1 en la hoja, coloque A1 en el primer argumento (según nuestras recomendaciones); si se selecciona B2, utilice B2 para el primer argumento y así sucesivamente. Realmente no importa qué referencia use, siempre que coincida con la celda seleccionada actualmente.
Para obtener instrucciones paso a paso, consulte Cómo crear una fórmula con nombre en Excel.
3. Configurar la validación de datos
Seleccione la primera celda que se verificará (A5 en nuestro caso) y cree una regla de validación de datos personalizada basada en la fórmula nombrada. Para hacer esto, haga lo siguiente:
- Hacer clic datos pestaña > Fecha de validación.
- En el Permitir lista desplegable, seleccione Personalizado.
- Ingrese la fórmula a continuación en el cuadro correspondiente.
=Validate_SKU - desmarcar Ignora la brecha opción, de lo contrario su regla no funcionará.
Opcionalmente, puede ingresar un mensaje de error personalizado que se mostrará cuando se ingresen datos no válidos en una celda.
Si cree que necesita los pasos detallados, aquí está: Cómo configurar la validación de datos personalizados en Excel.
4. Copie la validación de datos en varias celdas
Para copiar la configuración de validación en varias celdas, esto es lo que debe hacer:
- Seleccione la celda con validación de datos y presione Ctrl + C para copiarlo.
- Seleccione otras celdas que desee validar, haga clic derecho en ellas, haga clic en Pascua especialy elige Validación opción.
- Hacer clic Bien.
Puede encontrar más información en Cómo copiar la validación de datos.
Ahora, cada vez que alguien intente ingresar un SKU no válido en cualquiera de las celdas validadas, aparecerá el siguiente mensaje de advertencia:
Validación de correo electrónico con Regex
Para realizar la validación de correo electrónico, comience escribiendo una expresión regular para que coincida con una dirección de correo electrónico.
Modelo: ^[w.-]+@[A-Za-z0-9]+[A-Za-z0-9.-]*[A-Za-z0-9]+.[A-Za-z]{2.24}$
Para obtener una explicación detallada de la sintaxis, consulte Regex para hacer coincidir direcciones de correo electrónico válidas.
Y ahora, especifique los criterios de validación realizando los pasos ya familiares:
- Ingrese la expresión regular anterior en B2.
- Seleccione la celda A1 y defina un nombre llamado Validar el correo electrónico que se refiere a:
=RegExpMatch(Sheet1!A1, Sheet1!$B$2)
- Para la celda B5, aplique la validación de datos personalizada utilizando la fórmula a continuación. Es esencial que Ignora la brecha la opción debe ser deseleccionada.
=Validate_Email
Además, puede configurar un mensaje de error personalizado que solicite al usuario que ingrese una dirección de correo electrónico válida.
- Copie la regla en las celdas de abajo.
Si una dirección de correo electrónico que ingresa en una celda validada no coincide con un patrón de expresiones regulares, aparecerá la siguiente alerta:
Validación de contraseña usando expresiones regulares
Al usar expresiones regulares para la validación de contraseñas, lo primero que debe decidir es exactamente qué debe verificar su expresión regular. Aquí hay algunos ejemplos que pueden llevarlo por el camino correcto.
Una contraseña debe tener al menos 6 caracteres y solo puede contener letras (mayúsculas o minúsculas) y números:
Modelo: ^[A-Za-z0-9]{6,}$
Una contraseña debe tener al menos 6 caracteres e incluir al menos una letra y un número:
Modelo: ^(?=.*[A-Za-z])(?=.*d)[A-Za-zd]{6,}$
Una contraseña debe tener al menos 6 caracteres e incluir al menos una letra mayúscula, una letra minúscula y un número:
Modelo: ^(?=.*[A-Z])(?=.*[a-z])(?=.*d)[A-Za-zd]{6,}$
Una contraseña debe tener al menos 6 caracteres e incluir al menos una letra, un número y un carácter especial:
Modelo: ^(?=.*[A-Za-z])(?=.*d)(?=.*[@$!%*#?&_-])[A-Za-zd@$!%*#?&_-]{6,}$
Con el modelo establecido, puede pasar a configurar la validación de datos:
- Ingrese la contraseña de expresión regular en C2.
- Seleccione la celda A1 y cree una fórmula llamada Validar_contraseña:
=RegExpMatch(Sheet1!A1, Sheet1!$C$2)
- Para la celda C5, cree una regla de validación personalizada con la siguiente fórmula. No olvides deseleccionar Ignora la brecha Casilla de verificación.
=Validate_Password
- Copie la regla en tantas celdas como desee.
Ahora puede agregar con seguridad nuevas contraseñas a la lista. Si una cadena de entrada no coincide con la expresión regular, la siguiente alerta le recordará qué tipos de valores se admiten:
La validación de datos Regex no funciona
Si Regex Data Validation no funciona en Excel, lo más probable es que se deba a uno de los siguientes motivos.
Falta la función RegExpMatch
Antes de aplicar la validación de datos, asegúrese de ingresar el código de función RegExpMatch en su libro de trabajo.
Expresión regular incorrecta
Para asegurarse de que la expresión regular funcione como se espera, puede ingresar una fórmula RegExpMatch en una celda y examinar los resultados. Para obtener más información, consulte Coincidencia de expresiones regulares de Excel con ejemplos.
Para analizar y depurar expresiones regulares, puede usar servicios gratuitos de prueba de expresiones regulares en línea como RegEx101 o RegExr.
Fórmula mal nombrada
Una razón muy común por la que falla la validación de datos es una fórmula Regex que se refiere a la celda incorrecta. En todos los ejemplos, recomendamos definir una fórmula relacionada con A1:
=RegExpMatch(A1, regex)
Esto solo funciona si la celda A1 está activo al definir un nombre y un referencia relativa (sin el signo $).
La idea es que una referencia relativa especificada en la fórmula (A1) cambie automáticamente según la posición relativa de la celda validada. En otras palabras, la celda A1 se elige solo por conveniencia y consistencia. De hecho, puede seleccionar la celda B1 y hacer referencia a B1, puede seleccionar la celda C1 y hacer referencia a C1, y así sucesivamente. la clave es que la celda referenciada Debería ser Célula activa.
Para verificar que la fórmula nombrada es correcta, seleccione cualquier celda en la hoja de trabajo, abra el Administrador de nombres y vea a qué celda apunta la fórmula. Si se refiere a la celda actualmente seleccionada, la fórmula es correcta. De lo contrario, debe cambiar la referencia en el primer argumento.
En la siguiente captura de pantalla, la celda A7 está seleccionada, lo que significa que una fórmula con nombre debe tener A7 en su primer argumento. El segundo argumento ($A$2) se refiere a la expresión regular: esta referencia debe permanecer constante, por lo que está bloqueada con el signo $.
Ignorar la opción vacía seleccionada
Al configurar una regla de validación de datos personalizada, es importante anular la selección Ignora la brecha Casilla de verificación. De lo contrario, la regla no funcionará por el siguiente motivo:
Si no se encuentra ninguna coincidencia, la función RegExpMatch devuelve FALSO. Con Ignora la brecha opción seleccionada, FALSO es igual a vacío y se ignora.
Una solución alternativa establece explícitamente que la fórmula debe devolver VERDADERO:
=RegExpMatch(…)=TRUE
Así es como se realiza la validación de datos en Excel usando expresiones regulares. ¡Gracias por leer y espero verte la próxima semana en nuestro blog!
Libro de ejercicios para descargar.
Ejemplos de validación de datos Regex (archivo .xlsm)
