Hace un par de días @Jaume Feliu avisaba aquí de que la función ISEMAIL() de las HdC no identificaba correctamente desde hace un tiempo determinadas direcciones en el caso de que incluyeran ciertos TLDs (dominios de primer nivel, la cadena de texto a la derecha del último "."). Por ejemplo, .cat, .online o .eu, todos ellos perfectamente válidos. Es decir:

=ISEMAIL("pablo@midominio.eu") >> FALSO

ISEMAIL fallando con los dominios .eu y .online (por ejemplo).
ISEMAIL fallando con los dominios .eu y .online (por ejemplo).

En el hilo anterior proponía una fórmula, un tanto rebuscada, para solucionarlo, al menos hasta que ISEMAIL vuelva a funcionar correctamente. La desarrollo en hilo aparte (este) porque quizás el proceso para construirla sea de interés para los friquis de las hojas de cálculo 🤓.

Los TLDs los gestiona la IANA, y resulta que en su web he encontrado un archivo de texto plano, que parece permanentemente actualizado a tenor de la primera línea, con todos los TLDs admitidos.

Una bonita lista ¿oficlal? de TLDs.
Una bonita lista ¿oficlal? de TLDs.

Lo primero será traernos esta lista a una HdC. Para ello contamos con la función IMPORTDATA, que de un modo similar a IMPORTRANGE, lee "cosas" a partir de una URL que le facilitamos. En este caso lo que espera encontrarse es un archivo de texto con campos separados por comas o tabuladores.

=IMPORTDATA( "http://data.iana.org/TLD/tlds-alpha-by-domain.txt" )

IMPORTDATA permite recuperar el contenido de la lista de TLDs de la IANA.
IMPORTDATA permite recuperar el contenido de la lista de TLDs de la IANA.

Como veis, cada TLD (fila del archivo) se carga en una fila de la hoja de cálculo. Un sencillo...

=COUNTA(D3:D)

...nos indica que hay nada menos que 1526 TLDs válidos a 13/09/19.

Como la primera fila (# Versión 2019...) nos molesta, la excluimos de la lista entubando el resultado de la importación a un QUERY que tan solo selecciona los registros (filas) que no comiencen con el carácter #. Esto se consigue con la cláusula NOT ... LIKE dentro del SELECT del QUERY. Col1 es un nombre simbólico (característica poco documentada de QUERY) que representa el primer campo (columna) que resulta de la importación.

=QUERY( IMPORTDATA( "http://data.iana.org/TLD/tlds-alpha-by-domain.txt" ) ; "select Col1 where not Col1 like '#%'" )

Con QUERY nos quedamos solo con lo que nos interesa.
Con QUERY nos quedamos solo con lo que nos interesa.

Hacemos una pausa en este desarrollo para hablar de la enigmática y a menudo inefable función REGEXMATCH. Con ella podemos verificar si una cadena de texto facilitada tiene una determinada estructura o contiene un cierto patrón. Para indicarle a REGEXMATCH cuál es ese patrón se emplean las llamadas expresiones regulares, artilugios muy utilizados en diversos ámbitos relacionados con la programación, informática teórica, lenguajes formales, etc. Estas expresiones regulares, secuencias de texto en definitiva, tienen una sintaxis que puede ir de lo obvio a lo brutalmente críptico pero, sin embargo, dotan a REGEXMATCH de una potencia inusitada. Veamos un ejemplo:

=REGEXMATCH( "pablo@midominio.eu" ; "^[\w-.]+@([\w-]+\.)+[\w-]+$" )

La expresión anterior devuelve un valor VERDADERO dado que la cadena de texto consta de 3 partes: texto + "@" + texto + "." + texto.

Expresión regular vs. realidad.
Expresión regular vs. realidad.

Pero claro, este patrón casa con la estructura general de un email (@, punto, etc.), pero en ningún caso verifica que el TLD (lo que va tras el último punto) sea válido. Por tanto, se traga cosas que no debería.

Nuestra expresión regular se lo traga (casi) todo.
Nuestra expresión regular se lo traga (casi) todo.

Afortunadamente podemos especificar en nuestra expresión regular la existencia de secuencias de texto alternativas válidas. Por ejemplo:

=REGEXMATCH( "pablo@midominio.eu" ; "^[\w-.]+@([\w-]+\.)+(eu|cat)$" )

Hemos sustituido el subpatrón:

[\w-]+ >> secuencia de caracteres alfanuméricos y guiones

por:

(eu|cat) >> coincidencia exacta con la subcadena eu o cat.

La barra vertical "|" por tanto permite indicar esa alternancia admitida en nuestro patrón. De este modo se admiten las cadenas de texto que parecen un email y finalizan en .eu o .cat (y solo esas, claro).

Seguramente ya veis por donde voy. Retomo ahora el último QUERY + IMPORTDATA donde los habíamos dejado. Parece que no se han movido.

Lo que necesitamos es construir una megacadena de texto que concatene todos los dominios importados, utilizando "|" como separador. Para eso está la función JOIN:

=JOIN( "|" ; QUERY( IMPORTDATA( "http://data.iana.org/TLD/tlds-alpha-by-domain.txt" ) ; "select Col1 where not Col1 like '#%'" ) )

Parseemos la lista de TLDs.
Parseemos la lista de TLDs.

Ya tenemos una megacadena de TLDs separados por '|', que es el caracter que REGEXMATCH interpreta como 'o'. Pongámoslo todo juntito y convirtamos el email a chequear y la cadena de TLDs a minúsculas (función MINUSC) porque los patrones de REGEXMATCH diferencian A de a. Además, como las expresiones regulares también son sensibles a la presencia de espacios anteriores o posteriores, mejor eliminémoslos de la celda / cadena que contiene el email a verificar (función ESPACIOS). Por último, para incluir una expresión con funciones como parte de la expresión regular que, recordemos, es una cadena, recurriremos el operador de concatenación de cadenas (&). 

La fórmula queda así, ya totalmente montada y referenciando el email a validar en la celda A2:

=REGEXMATCH( MINUSC( ESPACIOS( A2 ) ) ; "^[\w-.]+@([\w-]+\.)+(" & MINUSC( JOIN( "|" ; QUERY( IMPORTDATA( "http://data.iana.org/TLD/tlds-alpha-by-domain.txt" ) ; "select Col1 where not Col1 like '#%'" ) ) ) & " )$" )

La fórmula con todos sus componentes encajados.
La fórmula con todos sus componentes encajados.

Pues ya está. Seguro que esto se puede mejorar, ¿te animas?

Hale, a tomar el vermú y ver la final de basket 😬.