Validación de direcciones de e-mail en Excel

martes, enero 01, 2013

Validación de datos es una herramienta excelente, indispensable diría, en todo modelo de Excel que requiera ingresar datos. Pero, como todo en este mundo, tiene sus limitaciones. Por ejemplo, ¿cómo comprobamos que el usuario ingrese direcciones válidas de correos electrónicos?

Si se tenemos una lista de direcciones válidas, nos basta con usar la opción Lista de validación de datos. Pero, ¿qué hacemos si no contamos con una lista de direcciones pero queremos asegurarnos que la sintaxis de la dirección es correcta? Es decir, abc123@xyz.com es una dirección válida, independientemente de que exista o no; pero abc..123@xyz no lo es).

La solución es usar expresiones regulares (regular expresions). La expresión regular es un patrón que nos permite describir un conjunto de texto sin enumerar sus elementos. No voy entrar en los detalles técnicos, que pueden leer en la nota del enlace, pero si señalar que estos patrones pueden ser usados en Vba. De esta manera podemos crear una función definida por el usuario para validar direcciones de correo electrónico.

La función para validar la sintaxis de una dirección de correo electrónico es

Public Function blnEmailValid(ByVal strEmailAdd As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Global = True
        .Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
        blnEmailValid = .Test(strEmailAdd)
    End With
End Function


Este código fue publicado por Vishesh en ExcelExperts.com.

En este ejemplo, ponemos la función en la celda D2 para evaluar la dirección ingresada en la celda C2; si la dirección es válida el valor en la celda D2 es VERDADERO, en caso contrario FALSO



También podemos usarla en Validación de Datos, usando el valor de la celda D2 en la opción Personalizada (en este ejemplo, ponemos la función en A2 que podamos hemos ocultado)



20 comments:

Juan Gabriel 02 enero, 2013 03:19  

¡Muchas gracias, Jorge! Disculpame mi ignorancia, quisiera consultarte cómo podría colocar la función en la celda D2 y en la validación de datos para que pueda funcionar.

Jorge L. Dunkelman 02 enero, 2013 07:35  

Juan Gabriel,
el código de la función hay que ponerlo en un módulo común del editor de Vb (puedes acceder al editor desde el menú Programador--Visual Basic o con el atajo Alt+F11).
Una vez instalada la función, puedes usarla como en el ejemplo: la ponemos en la celda A2 y en validación de datos-fórmula creamos una referencia a esa celda.

Juan Gabriel 02 enero, 2013 23:34  

Muchas gracias Jorge, estuve probando y cuando pongo la formula en la celda y presiono enter, aparece el error NOMBRE. ¿Que es el modulo común?

Jorge L. Dunkelman 04 enero, 2013 11:52  

Juan Gabriel, como puse en el comentario anterior, hay que activar el editor de Vb, agregar un módulo con el menú Insert-Module y en él pegar el código.

Juan Gabriel 06 enero, 2013 18:49  

¡Muchísimas gracias, Jorge, ahora sí me funcionó! ¿Qué significa "insertar un módulo? Perdón por tantas preguntas.

Fernando G. 07 febrero, 2013 00:23  

Buenas tardes. Muchas gracias por el código de verdad es muy útil. Me gustaría saber cuál es la fórmula que ingresas en la validación de datos personalizada para lograr el efecto mostrado en la validación. Pongo la fórmula (blnEmailValid) en la celda A2 y trato de hacer la validación, obligando a que la celda sea "verdadero" pero así escriba un mail correctamente, el argumento no alcanza a cambiar de falso a verdadero, por lo que no logro avanzar.

De nuevo muchas gracias por su valiosa ayuda.

Jorge L. Dunkelman 07 febrero, 2013 07:40  

Como pongo en la nota, en la validación de datos uso, sencillamente, =A2.
Cualdo el valor en la celda de control (A2) es FALSO, validación de datos no acepta ese valor.
No se pueden usar funciones definidas por el usuario directamente en validación de datos.

Juani 08 febrero, 2013 20:43  

Hola Jorge, en una aplicación en la que estuve trabajando yo validaba la entrada de email con esta formula:
=Y(HALLAR("*@*.*";A1);ESERROR(HALLAR(" ";A1)))
donde el pattern es *@*.* y además tiene que cumplir que no haya espacios en el string.

Es básica y se puede mejorar pero sirve.
Yo la use dentro de "Validación de datos -> Personalizada"

¿Se podría utilizar una expresión regular dentro de una formula?

Jorge L. Dunkelman 09 febrero, 2013 10:05  

Juani, gracias por colaborar.
Toda función definida por el usuario, como la que muestro en esta nota, se puede usar en una fórmula.

Anónimo,  14 agosto, 2013 21:10  

Impecable, muchas gracias!!!!!!

Jaime Cruz 20 noviembre, 2013 20:18  

La expresión regular está mal ya que no admite por ejemplo dominios .info . Me imagino porque se habrá puesto en la expresión regular que a partir del punto solo se admitan 3 caracteres.

Alicia Olivares Barriga 26 marzo, 2014 11:19  

hola, juani,
yo estoy probando tu formula con HALLAR...y me parece bastante buena.
Tambien he porbado a hacerlo usando la opción de validación de listas y pegando la expresion pattern en la opcion de personalizada (es decir, que la meto como fórmula directamente) pero me da peor resultado. En conclusion juani, creo que tu formula es más sencilla y util.

Saludos!

Jorge Dunkelman 26 marzo, 2014 13:48  

Efectivamente, la fórmula de Juani es sencilla, pero sólo controla que el texto contenga el símbolo @ y un punto después. Por ejemplo, la dirección A@b@c@example.com es válida si la controlamos con la fórmula de Juani pero en realidad no lo es (se admite sólo un sómbolo@ en la dirección); lo mismo sucedería con la dirección A@email.b
¿Cuáles son los problemas que encuentras con el uso de la expresión regular?

Anónimo,  10 abril, 2015 06:49  

El problema es que si antes de cambiar el texto de la celda C2, la celda D2 estaba en falso, la validación es incorrecta ya que en la secuencia de actualización de Excel, primero valida y luego cambia D2 de Falso a Verdadero

Jorge Dunkelman 10 abril, 2015 11:10  

La validación funciona. Para simplificar puedes hacer esta prueba:
# - en la celda D2 ponemos la expresión =C2>5
# - como la celda C2 está vacía, veremos FALSO en la celda D2
# - ahora ponemos 4 en la celda C2 lo que dispara la validación de datos no permitiendo poner ese valor en la ceda. Si optamos por cancelar, la celda C2 sigue vacía y D2 sigue mostrando FALSO
# - introducimos 6 en C2; el valor es aceptado y D2 muestra ahora VERDADERO.

Unknown 20 octubre, 2015 03:09  

Buenas tardes Jorge.
Estpy de acuerdo contigo en que utilizando la RegExp es mas seguro, pero se me está presentando un problemka distinto.
La idea es que en una TABLA se realice la validación del dato ingresado por el usuario, sin que le permita continuar si la dirección de correo no es estructuralmente válida. Mi problema consiste en al ingresar la nueva funcion dentro de la tabla me arroja el error NO SE PUEDE ENCONTRAR UNO DE LOS RANGOS
Me podrias ayudar?
Gracias de antemano

Jorge Dunkelman 20 octubre, 2015 15:31  

Para realizar la validación hay que usar la la UDF en el formulario de validación de datos. ¿Pod´rias explicar un poco más cómo estás usando al función?

Fred 11 noviembre, 2015 00:14  

Buenísimo el tema, me han ayudado muchísimo

Unknown 06 abril, 2016 01:25  

a mi se me ocurrio hacerla asi, tal vez sea muy rebuscada, pero me funciono:

=Y(NO(ESERROR(SI(ENCONTRAR("@",M2,2),VERDADERO,FALSO))),ESERROR(ENCONTRAR("@",M2,((ENCONTRAR("@",M2,1)+1)))),ESERROR(ENCONTRAR(" ",M2,1)),NO(ESERROR(ENCONTRAR(".",M2,((ENCONTRAR("@",M2,1)+2))))))

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP