Calcular y comprobar el dígito de control EAN en Excel y Hojas de cálculo de Google, con fórmulas listas para copiar

¿Cómo compruebo una columna entera de códigos EAN en Excel?

Pon los códigos en la columna A, a partir de la fila 2. Pega la fórmula en B2 y cópiala hacia abajo: arrastra el controlador de relleno (el cuadradito de la esquina inferior derecha de la celda) o haz doble clic en él. Con un triple clic en la fórmula de abajo la seleccionas entera.

=SI.ERROR(SI(A2="";"";SI(ESNUMERO(HALLAR("E+";A2));"notación científica";SI(ESERROR(SUMAPRODUCTO(EXTRAE(A2;FILA(INDIRECTO("1:"&LARGO(A2)));1)*1));"contiene otros caracteres";SI(O(LARGO(A2)=8;LARGO(A2)=12;LARGO(A2)=13;LARGO(A2)=14);SI(RESIDUO(SUMAPRODUCTO(EXTRAE(DERECHA(REPETIR("0";14)&A2;14);FILA(INDIRECTO("1:14"));1)*(1+2*RESIDUO(FILA(INDIRECTO("1:14"));2)));10)=0;"OK";"dígito de control incorrecto");"longitud incorrecta: "&LARGO(A2)))));"contiene otros caracteres")

Resultado	Qué significa
OK	8, 12, 13 o 14 dígitos, y el dígito de control es correcto; por ejemplo, 5901234123457
dígito de control incorrecto	el último dígito no coincide con el que sale de los demás; suele deberse a un dígito mal tecleado, como en 5901234123450
longitud incorrecta: 11	otro número de dígitos; 11 suele ser un UPC-A que ha perdido el cero inicial, como 12345678905
contiene otros caracteres	un espacio, un guion, una letra, un espacio de no separación, un relleno como «n/d» o un error de otra fórmula, como en 590-1234-12345
notación científica	el código está en la celda como texto del tipo 5,90123E+12, y sus últimos dígitos se han perdido

OK significa que el código está bien formado. Si ese número existe es otra cuestión; lo explicamos más abajo.

Hemos probado las dos fórmulas, escritas con los nombres de función en inglés, en Excel 2021 con los códigos de las tablas de esta página: todos los resultados coinciden con las tablas. Los nombres en español son los de la ayuda de Microsoft en español. En un Excel con la interfaz en español todavía no las hemos probado.

¿Cómo calculo el dígito de control en Excel?

Esta fórmula es para códigos sin su último dígito: 7 dígitos para un EAN-8, 11 para un UPC-A, 12 para un EAN-13 y 13 para un GTIN-14. El resultado es texto, así que Excel no lo convierte en 5,90123E+12 ni le quita un cero inicial.

=SI.ERROR(SI(A2="";"";SI(ESERROR(SUMAPRODUCTO(EXTRAE(A2;FILA(INDIRECTO("1:"&LARGO(A2)));1)*1));"contiene otros caracteres";SI(O(LARGO(A2)=7;LARGO(A2)=11;LARGO(A2)=12;LARGO(A2)=13);A2&RESIDUO(-SUMAPRODUCTO(EXTRAE(DERECHA(REPETIR("0";13)&A2;13);FILA(INDIRECTO("1:13"));1)*(1+2*RESIDUO(FILA(INDIRECTO("1:13"));2)));10);"longitud incorrecta: "&LARGO(A2))));"contiene otros caracteres")

En la columna A	Resultado
590123412345	5901234123457
9638507	96385074
01234567890	012345678905
1590123412345	15901234123454

Úsala solo con códigos que todavía no tengan dígito de control. Un EAN-13 completo también tiene 13 dígitos: la fórmula le añadiría un decimocuarto, y el resultado ya no sería un código de ese producto.

¿Punto y coma o coma?

Las fórmulas de arriba separan los argumentos con punto y coma, que es lo que espera Excel cuando el separador decimal es la coma, como en España. Si tu configuración regional usa el punto decimal, como en México, Excel espera comas: cambia todos los punto y coma por comas con Buscar y reemplazar. En los textos entre comillas de estas fórmulas no hay ningún punto y coma, así que no se rompe nada.

Excel en inglés: las mismas fórmulas con los nombres en inglés

Un Excel en inglés no reconoce los nombres de función en español. Si tiene la configuración regional de España (coma decimal), pega estas versiones, con los nombres en inglés y punto y coma:

=IFERROR(IF(A2="";"";IF(ISNUMBER(SEARCH("E+";A2));"notación científica";IF(ISERROR(SUMPRODUCT(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)*1));"contiene otros caracteres";IF(OR(LEN(A2)=8;LEN(A2)=12;LEN(A2)=13;LEN(A2)=14);IF(MOD(SUMPRODUCT(MID(RIGHT(REPT("0";14)&A2;14);ROW(INDIRECT("1:14"));1)*(1+2*MOD(ROW(INDIRECT("1:14"));2)));10)=0;"OK";"dígito de control incorrecto");"longitud incorrecta: "&LEN(A2)))));"contiene otros caracteres")

=IFERROR(IF(A2="";"";IF(ISERROR(SUMPRODUCT(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)*1));"contiene otros caracteres";IF(OR(LEN(A2)=7;LEN(A2)=11;LEN(A2)=12;LEN(A2)=13);A2&MOD(-SUMPRODUCT(MID(RIGHT(REPT("0";13)&A2;13);ROW(INDIRECT("1:13"));1)*(1+2*MOD(ROW(INDIRECT("1:13"));2)));10);"longitud incorrecta: "&LEN(A2))));"contiene otros caracteres")

Con el punto decimal (por ejemplo, la configuración regional de Estados Unidos), Excel espera comas en lugar de punto y coma; también aquí basta con cambiarlos todos. La versión con comas está en la página en inglés.

Hojas de cálculo de Google acepta los nombres en inglés aunque muestre las funciones en otro idioma: lo hemos comprobado con el polaco. El separador depende de la configuración regional de la hoja de cálculo, igual que en Excel.

¿Cómo funcionan las fórmulas?

Las dos empiezan rellenando el código con ceros por la izquierda hasta 14 dígitos: DERECHA(REPETIR("0";14)&A2;14). Los ceros a la izquierda no cambian el dígito de control, así que una sola fórmula sirve para EAN-8, UPC-A, EAN-13 y GTIN-14. EXTRAE con FILA(INDIRECTO("1:14")) separa el código en dígitos, y 1+2*RESIDUO(FILA(…);2) les da su peso: 3 en las posiciones impares y 1 en las pares. En un código de 14 dígitos es la regla de siempre: desde la derecha, 3 y 1 alternados, empezando por el dígito que está justo antes del dígito de control. SUMAPRODUCTO multiplica y suma. El dígito de control entra en la suma con peso 1, así que en un código correcto el total es múltiplo de 10, y eso es lo que comprueba RESIDUO(…;10)=0.

La segunda fórmula hace lo mismo con 13 posiciones y añade RESIDUO(-suma;10): lo que le falta a la suma para llegar al siguiente múltiplo de diez, o 0 si ya lo es. El método, con un ejemplo paso a paso, está en la página Validar códigos EAN.

Las fórmulas son largas a propósito. No llevan constantes matriciales (valores entre llaves), porque Excel espera en ellas separadores distintos según el idioma. Tampoco llevan LET ni SECUENCIA, porque Excel 2019 y las versiones anteriores no tienen esas funciones. La función más reciente que usan es SI.ERROR (de Excel 2007), y SUMAPRODUCTO no necesita Ctrl+Mayús+Entrar.

Antes de pegar códigos en una hoja de cálculo

Excel y Hojas de cálculo de Google toman un código por un número. Los ceros iniciales desaparecen, y Excel muestra un número de 13 dígitos como 5,90123E+12. Mientras la celda guarde el número, no se pierde nada: la fórmula lee el valor completo y da OK. Los dígitos se pierden cuando Excel guarda la hoja como CSV, porque entonces en el archivo queda escrito el texto 5,90123E+12. Un código así solo se puede recuperar de donde salió: la tienda, el archivo del proveedor, el envase.

Para que los códigos se queden exactamente como están, da formato de texto a la columna antes de pegarlos: en Excel, Inicio → Formato de número → Texto; en Hojas de cálculo de Google, Formato → Número → Texto sin formato.

¿Qué no comprueban las fórmulas?

• Si el número existe y pertenece a este producto. Eso solo lo sabe el registro de GS1: puedes buscar un código en Verified by GS1.
• Los rangos que Google Merchant Center no acepta: códigos internos que empiezan por 2, 02 o 04 y códigos de cupones que empiezan por 05, 98 o 99 (en la forma de 13 dígitos). Con ellos la fórmula da OK.
• Los duplicados, incluido el mismo código escrito de dos formas, como 012345678905 y 0012345678905.
• Un código formado solo por ceros: 0000000000000 tiene un dígito de control correcto.
• El ISBN-10, que tiene su propio dígito de control y puede terminar en X. La fórmula da longitud incorrecta: 10 o contiene otros caracteres. Un ISBN-13 es un EAN-13 y se comprueba como tal.

¿Cómo compruebo un archivo entero de una vez?

El validador de códigos EAN hace todo lo de esa lista salvo la consulta al registro de GS1, y sin fórmulas: pega una columna o abre la exportación de la tienda en CSV o XLSX. Encuentra los duplicados aunque estén escritos de forma distinta, dice qué carácter invisible hay en un código y da el número de fila de cada problema. El archivo no sale de tu navegador.

En Hojas de cálculo de Google, el complemento gratuito GTIN & EAN Check (en inglés) hace lo mismo sin salir de la hoja: selecciona la columna y elige Extensiones → GTIN & EAN Check → Check selected codes. Los códigos que hay que corregir van a un informe en una hoja nueva, con un enlace a cada celda.