¿Te has encontrado alguna vez con una hoja de Excel llena de datos desorganizados o erróneos? La validación de datos es esencial para garantizar la precisión y la fiabilidad de tu trabajo, y en este artículo te enseñaremos cómo las expresiones regulares (Regex) pueden ser tu mejor aliada en este proceso. Las Regex son herramientas poderosas que permiten identificar patrones en cadenas de texto, haciendo que la validación de datos en Excel sea más eficiente y precisa. Acompáñanos en este viaje y descubre cómo transformar tus hojas de cálculo en fuentes de información confiables y ordenadas con la magia de las expresiones regulares. ¡Comencemos!
En el mundo de los datos, la precisión y la validez son aspectos fundamentales para garantizar la calidad de la información. En este sentido, Excel es una herramienta ampliamente utilizada para el manejo de datos, pero ¿cómo podemos asegurarnos de que nuestra información sea correcta y cumpla con los criterios deseados? Aquí es donde entran en juego las expresiones regulares (regex). En este artículo, exploraremos cómo podemos utilizar las regex para validar y filtrar los datos en Excel, asegurándonos de que nuestra información sea precisa y confiable. ¡Acompáñanos en esta fascinante aventura de validación de datos con expresiones regulares!
El tutorial muestra cómo realizar la validación de datos en Excel usando expresiones regulares con la ayuda de una función RegexMatch personalizada.
Cuando se trata de restringir la entrada del usuario en hojas de cálculo de Excel, la validación de datos es indispensable. ¿Quiere permitir solo números o fechas en una celda determinada? ¿O limitar los valores del texto a una longitud específica? ¿O tal vez no permitir tiempos fuera de un rango determinado? No hay problema, todo esto se puede hacer fácilmente con criterios de validación preestablecidos o personalizados. Pero, ¿qué pasa si quiero permitir sólo direcciones de correo electrónico válidas o cadenas que coincidan con un patrón específico? Por desgracia, eso no es posible. ¿Expresiones regulares dices? Mmmm… ¡eso podría funcionar!
Cómo hacer la validación de datos de Excel con Regex
Lamentablemente, 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 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 enviar directamente a la validación de datos; necesitará un mediador en forma de fórmula con nombre.
Teniendo en cuenta lo anterior, describamos brevemente los pasos a seguir para validar datos en Excel usando expresiones regulares:
¿Suena como un plan? ¡Intentemos implementarlo en la práctica!
Validación de datos de Excel mediante expresiones regulares personalizadas.
Este ejemplo aborda un caso muy común: cómo permitir solo los valores de un patrón específico.
Supongamos que mantiene algunos códigos SKU en su hoja de trabajo y quiere asegurarse de que solo los códigos que coinciden con un patrón determinado entren en la lista. 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 siguiente expresión regular.
Patrón: ^[A-Z]{3}-d{3}$
Tenga en cuenta que el inicio (^) y el final ($) de la cadena están anclados, por lo que no se pueden ingresar en una celda caracteres que no sean los del patrón.
1. Agregue una función Regex Match personalizada
Comience insertando la función RegExpMatch en su libro de trabajo. El código ya está escrito por nuestros gurús de Excel, por lo que sólo necesita copiarlo desde la página vinculada anteriormente y pegarlo en su editor VBA.
Aquí está la sintaxis de la función para su referencia:
RegExpMatch(texto, patrón, [match_case])
Dónde:
- Texto (obligatorio): una cadena de origen (en nuestro contexto, una celda validada).
- Patrón (obligatorio): una expresión regular que coincida.
- caso_partido (opcional) – tipo de coincidencia. VERDADERO u omitido: distingue entre mayúsculas y minúsculas; FALSO: no distingue entre mayúsculas y minúsculas.
Consejo. Si es usuario de nuestra Ultimate Suite, puede realizar la validación de datos Regex en Excel sin agregar ningún código VBA a sus libros. Simplemente aproveche una función personalizada AblebitsRegexMatch incluida con nuestras herramientas Regex.
2. Crea una fórmula con nombre
En su hoja de trabajo de destino, seleccione la celda A1 (independientemente de su contenido y sin importar qué celda realmente va a 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 alguna 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 utilizar una referencia relativa para el texto argumento (A1) y referencia absoluta para patrón (2 dólares australianos).
Dado que nuestra fórmula está destinada a validar números de SKU, la denominamos en consecuencia: Validar_SKU.
>
¡Nota IMPORTANTE! Al definir la fórmula, verifique que la 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 particular utilices siempre y cuando coincida con la celda actualmente seleccionada.
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 a verificar (A5 en nuestro caso) y cree una regla de validación de datos personalizada basada en la fórmula nombrada. Para esto, haga lo siguiente:
=Validate_SKU
>Opcionalmente, puede escribir 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í tiene: Cómo configurar la validación de datos personalizada en Excel.
4. Copie la validación de datos a más celdas
Para copiar la configuración de validación a más celdas, esto es lo que debe hacer:
>
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 del correo electrónico, comience escribiendo una expresión regular que coincida con una dirección de correo electrónico.
Patrón: ^[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 que ya conoce:
=RegExpMatch(Sheet1!A1, Sheet1!$B$2) 
>
=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.
Si una dirección de correo electrónico que ingresa en una celda validada no coincide con un patrón de expresión regular, aparecerá la siguiente alerta:
>
Validar contraseñas usando expresiones regulares
Al utilizar 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 podrían ubicarlo en el camino correcto.
Una contraseña debe tener al menos 6 caracteres y solo puede contener letras (mayúsculas o minúsculas) y dígitos:
Patrón: ^[A-Za-z0-9]{6,}$
Una contraseña debe tener un mínimo de 6 caracteres e incluir al menos una letra y un dígito:
Patrón: ^(?=.*[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 dígito:
Patrón: ^(?=.*[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 dígito y un carácter especial:
Patrón: ^(?=.*[A-Za-z])(?=.*d)(?=.*[@$!%*#?&_-])[A-Za-zd@$!%*#?&_-]{6,}$
Con el patrón establecido, puedes pasar a configurar la Validación de datos:
=RegExpMatch(Sheet1!A1, Sheet1!$C$2) 
>
=Validate_Password 
>
Ahora puede agregar nuevas contraseñas a la lista de forma segura. Si una cadena de entrada no coincide con la expresión regular, la siguiente alerta le recordará qué tipos de valores se aceptan:
>
La validación de datos Regex no funciona
Si la validación de datos Regex no funciona en su 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 insertar el código de la función RegExpMatch en su libro de trabajo.
Expresión regular incorrecta
Para asegurarse de que su expresión regular funcione como se esperaba, puede ingresar una fórmula RegExpMatch en alguna celda y examinar los resultados. Para obtener más información, consulte Coincidencia de expresiones regulares de Excel con ejemplos.
Para analizar y depurar sus expresiones regulares, puede utilizar servicios gratuitos de prueba de expresiones regulares en línea, como RegEx101 o RegExr.
Fórmula con nombre incorrecto
Una razón muy común de falla en la validación de datos es una fórmula con nombre Regex que hace referencia a una celda incorrecta. En todos los ejemplos, recomendamos definir una fórmula referida a A1:
=RegExpMatch(A1, regex)
Esto solo funciona si el celular A1 está activo al definir un nombre y un referencia relativa (sin el signo $) se utiliza.
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 sólo por conveniencia y coherencia. De hecho, puede seleccionar la celda B1 y hacer referencia a B1, seleccionar la celda C1 y hacer referencia a C1, y así sucesivamente. La clave es que el celda referenciada debería ser el Célula activa.
Para comprobar si la fórmula nombrada es correcta, seleccione cualquier celda de su 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, deberías cambiar la referencia en el primer argumento.
En la captura de pantalla siguiente, se selecciona la celda A7, lo que significa que una fórmula con nombre debe tener A7 en el primer argumento. El segundo argumento ($A$2) se refiere a la expresión regular; se supone que esta referencia permanece constante, por lo que está bloqueada con el signo $.
>
Ignorar la opción en blanco seleccionada
Al configurar una regla de validación de datos personalizada, es importante deseleccionar la ignorar en blanco casilla de verificación. De lo contrario, la regla no funcionará por el siguiente motivo:
Si no se encuentra una coincidencia, la función RegExpMatch devuelve FALSO. Con el ignorar en blanco opción seleccionada, FALSE equivale a estar en blanco y se ignora.
Una solución alternativa es indicar 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. ¡Te agradezco por leer y espero verte en nuestro blog la próxima semana!
Libro de práctica para descargar.
Ejemplos de validación de datos Regex (archivo .xlsm)
Usted también podría estar interesado en
Validación de datos de Excel mediante expresiones regulares (Regex)
Validación de datos de Excel mediante expresiones regulares (Regex)
Las expresiones regulares (Regex) son una herramienta poderosa y versátil para validar y manipular datos en Excel. Con su capacidad para buscar patrones específicos en los datos, se convierten en una solución eficiente para verificar la precisión y consistencia de la información.
¿Qué es la validación de datos?
La validación de datos es un proceso que garantiza que los datos ingresados en una hoja de cálculo cumplan ciertos criterios predefinidos. Estos criterios pueden incluir restricciones de formato, valores numéricos dentro de un rango específico, longitud de caracteres, entre otros. La validación de datos ayuda a evitar errores y asegura la integridad de los datos.
¿Cómo utilizar expresiones regulares para validar datos en Excel?
Excel ofrece la capacidad de utilizar expresiones regulares para validar datos utilizando la función coincidir (MATCH) y la función extraer (EXTRACT). Estas funciones permiten buscar patrones específicos dentro de los datos y aplicar validaciones basadas en ellos.
Por ejemplo, si deseamos validar un campo que debe contener solo letras mayúsculas, podemos utilizar la siguiente expresión regular: [A-Z]+. Esta expresión buscará cualquier secuencia de una o más letras mayúsculas en el campo.
Otro ejemplo podría ser la validación de un número de teléfono que debe cumplir el formato específico de un código de área seguido de un guion y un número de teléfono. Podemos utilizar la expresión regular: d{3}-d{7} para validar si el número proporcionado cumple este patrón de formato.
¿Cuáles son las ventajas de utilizar expresiones regulares para la validación de datos en Excel?
El uso de expresiones regulares en la validación de datos en Excel ofrece varias ventajas:
Conclusión
La validación de datos es esencial para garantizar la precisión y confiabilidad de la información en Excel. Las expresiones regulares ofrecen una solución poderosa para validar datos al permitir la búsqueda de patrones específicos dentro de los mismos. Al utilizar funciones como coincidir y extraer en Excel, podemos aplicar fácilmente validaciones basadas en expresiones regulares. Beneficiarse de estas herramientas ayuda a mantener la integridad de los datos y mejora la calidad de los análisis y reportes generados en Excel.
Fuentes adicionales:
- Validar datos en celdas – Office Support
- Expresión regular – Wikipedia
- Regexr – Herramienta de expresiones regulares
[automatic_youtube_gallery type=»search» search=»Validación de datos de Excel mediante expresiones regulares (Regex)» limit=»1″]
La validación de datos en Excel usando expresiones regulares (Regex) es una técnica avanzada que permite asegurar que los datos cumplen con un formato específico. A continuación, te presento un resumen de los pasos esenciales para implementar esta funcionalidad, utilizando un caso práctico con códigos SKU.
Paso 1: Agregar la función Regex Match personalizada
Antes de comenzar, asegúrate de tener la función RegExpMatch disponible en tu libro de trabajo. Debes copiar el código VBA que define esta función desde tu fuente de confianza y pegarlo en el editor VBA de Excel.
Aquí te dejo un ejemplo de cómo podría lucir la función:
vba
Function RegExpMatch(text As String, pattern As String, Optional matchcase As Boolean = True) As Boolean
Dim regEx As Object
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = pattern
regEx.IgnoreCase = Not matchcase
regEx.Global = True
RegExpMatch = regEx.Test(text)
End Function
Paso 2: Crear una fórmula con nombre
- Selecciona una celda en tu hoja de trabajo (por ejemplo, A1).
- Presiona
Ctrl + F3para abrir el Administrador de nombres. - Define un nuevo nombre, por ejemplo,
ValidarSKU. - En el campo de «Refiere a», ingresa la fórmula que usará
RegExpMatch:
=RegExpMatch(Sheet1!A1, "^[A-Z]{3}-d{3}$")
Nota: Asegúrate de usar referencias relativas para la celda que validas.
Paso 3: Configurar la validación de datos
- Selecciona la celda que deseas validar (por ejemplo, A5).
- Ve a la pestaña
Datosy seleccionaValidación de datos. - En el menú desplegable
Permitir, seleccionaPersonalizada. - En el campo de fórmula, ingresa:
=ValidarSKU
- Asegúrate de desmarcar la opción
Ignorar en blanco.
Paso 4: Copiar la validación de datos a más celdas
Para aplicar la misma validación a otras celdas:
- Selecciona la celda con la validación (A5) y presiona
Ctrl + C. - Selecciona las celdas adicionales donde deseas aplicar la misma validación.
- Haz clic derecho y elige
Pegado especial, luego seleccionaValidación.
Mensajes de advertencia
Cuando un usuario ingresa un SKU no válido, Excel mostrará un mensaje de advertencia. Si deseas, puedes personalizar este mensaje en la ventana de configuración de validación de datos.
Validar otros patrones
Puedes seguir un proceso similar para validar correos electrónicos, contraseñas u otros datos usando distintas expresiones regulares. Aquí te dejo algunos patrones útiles:
- Correo Electrónico:
regex
^[w.-]+@[A-Za-z0-9]+[A-Za-z0-9.-][A-Za-z0-9]+.[A-Za-z]{2,24}$
- Contraseña (mínimo 6 caracteres, al menos una letra y número):
regex
^(?=.[A-Za-z])(?=.*d)[A-Za-zd]{6,}$
Errores comunes
Si la validación no funciona, comprueba lo siguiente:
- Asegúrate de que la función
RegExpMatchestá correctamente definida y accesible. - Verifica que la expresión regular es correcta.
- Confirma que la fórmula con nombre hace referencia a la celda adecuada y utiliza la referencia correcta (relativa).
Conclusión
La validación de datos con expresiones regulares en Excel es una herramienta poderosa para asegurar la integridad de los datos. Siguiendo estos pasos, podrás implementar validaciones específicas que mejoren la calidad de la información en tus hojas de cálculo. Si tienes más preguntas o necesitas más ejemplos, ¡no dudes en preguntar!
Sewellxt: ¡Me encantó el artículo! No tenía idea de que podía usar expresiones regulares para validar datos en Excel. Una vez, me pasé horas limpiando un montón de datos porque tenía errores y, si hubiera sabido esto antes, me habría ahorrado un montón de tiempo. ¡Definitivamente lo probaré en mi próximo proyecto!
Isidro pedro: ¡Qué buen artículo! Yo tampoco sabía que podía usar Regex en Excel hasta hace poco. Me pasó algo similar a Sewellxt, pasé un montón de tiempo validando datos de una lista de contactos para un evento y si hubiera encontrado esta info antes, me habría ahorrado mucho estrés. ¡A ver si lo pongo en práctica ya!
Liturgiet: ¡Qué genial el artículo! Nunca pensé que Regex podría ser tan útil en Excel. Hace poco intenté organizar una lista de ventas y me volví loco con las direcciones, si hubiera tenido esta información antes, me hubiera evitado un caos total. ¡Definitivamente voy a ponerme las pilas y usarlo ya!
Musclecarstard: ¡Totalmente de acuerdo, Sewellxt! Yo también me quedé impresionado con lo que se puede hacer con Regex en Excel. Hace poco estuve tratando de dar sentido a un listado gigante de correos electrónicos y, si hubiera leído este artículo antes, me hubiera evitado un buen dolor de cabeza. ¡A ponerlo en práctica ya!