Formato de Celda Tipo DNI

Descargar el Archivo

"Todos los días tengo que introducir numerosos datos referentes al DNI de clientes. Me gustaría introducir en la celda una entrada del tipo 33444555b y que automáticamente se transformara en 33.444.555-B"

El problema tiene solución "razonablemente fácil" mediante fórmula en una nueva columna. Pero no podemos resolverlo directamente con Formato de Celda Personalizado porque excel reconoce números o textos pero no ambos a la vez.

Voy a proponer dos planteamientos distintos, a saber:

1. Tenemos dos columnas de entrada de datos. En la primera introducimos el número del DNI y en la segunda la letra

2. Tenemos una sola celda de entrada donde escribimos directamente el número y letra del DNI.

Aclaración: Los DNI utilizados en los siguientes ejemplos son inventados y, por lo tanto, no tienen por qué corresponderse con la letra adecuada. Al final de este post incluyo una fórmula para obtener la letra correspondiente a cada DNI.

Planteamiento 1:

1. Nos situamos en la celda D4 y escribimos la siguiente fórmula:

=TEXTO(B4;"#.##0-")&MAYUSC(C4)

(Consultar la función TEXTO) Es importante destacar que el formato establecido dentro de la función TEXTO entre comillas concluye con un guión al final.

El símbolo & corresponde a la función CONCATENAR.

Finalmente la función MAYUSC convierte un texto a mayúsculas. El resultado será:

Planteamiento 2:

1. Nos situamos en la celda C10 y escribimos la siguiente fórmula:

=TEXTO(IZQUIERDA(B10;LARGO(B10)-1);"#.##0-")&MAYUSC(DERECHA(B10;1))

Evidentemente la cosa se ha complicado un poco. El motivo es que pueden existir DNI con distintos números de dígitos. Empezando por el final:

MAYUSC(DERECHA(B10;1)) Esta parte de la fórmula convierte en MAYÚSCULA el caracter(uno solo) que se encuentre a la DERECHA del dato introducido en B10. Es decir, que extraemos la letra del final y la ponemos en mayúscula.

Ahora nos queda extraer los dígitos que compongan el número del DNI y darles el formato adecuado. Para extraerlos utilizamos la función LARGO anidada en la función IZQUIERDA. La función LARGO nos indica el número de caracteres que hay en una celda. Si por ejemploescribieramos en una celda la fórmula =LARGO ("hola") el resultado sería 4, que son el número de caracteres de dicha palabra. La parte de nuestra fórmula LARGO(B10)-1 nos devolverá el número total de caracteres existentes en B10 menos 1 (este menos 1 es para restarle la letra). Este número restante será el número de dígitos que compongan el DNI. Ya sólo nos queda extraer dicho número con la función IZQUIERDA: IZQUIERDA(B10;LARGO(B10)-1) y trabajo casiresuelto. Digo casi porque nos faltaría darle el formato adecuado, tarea que realizaremos, como ya sabemos, con la función TEXTO: TEXTO(IZQUIERDA(B10;LARGO(B10)-1);"#.##0-")

A estas alturas de la película ya tenemos el número por un lado y la letra por otro. Los unimos con el símbolo & (CONCATENAR) y trabajo resuelto.

Fórmula para el cálculo de la letra del DNI:

Con la siguiente fórmula puede obtener la letra correspondiente a un DNI determinado:

=ELEGIR(RESIDUO(B4;23)+1;"T";"R";"W";"A";"G";"M";"Y";"F";"P";"D";"X";"B";"N";"J"; "Z";"S";"Q";"V";"H";"L";"C";"K";"E";"T")

Haciendo uso de esta fórmula en nuestro primer planteamiento podríamos resolver directamente, sin necesidad de introducir la letra correspondiente en la columna C, de la forma: (pruebe a escribirlo en la celda, por ejemplo, E4)

=TEXTO(B4;"#.##0-")&

ELEGIR(RESIDUO(B4;23)+1;"T";"R";"W";"A";"G";"M";"Y";"F";"P";"D";"X";"B";"N";"J"; "Z";"S";"Q";"V";"H";"L";"C";"K";"E";"T")