Especificar Tipo de Formato de una Celda

"Regularmente me envían un listado con diferentes entradas en una columna y necesito detectar, por medio de fórmulas, cuáles de dichas entradas son fechas".

Para solucionar este problema haremos uso de la función CELDA. Partimos del siguiente ejemplo:

La función CELDA devuelve información acerca del formato, la ubicación o el contenido de una celda. La sintaxis de esta función es CELDA( tipo_de_info;referencia), tal y como muestra la ayuda de excel, y tiene los siguientes argumentos:

tipo_de_info: Es un valor de texto que especifica el tipo de información de la celda que se desea obtener. La siguiente lista muestra los posibles valores del argumento de tipo_de_info y los correspondientes resultados:

tipo_de_info	Devuelve
"DIRECCION"	la referencia, en forma de texto, de la primera celda del argumento ref.
"COLUMNA"	El número de columna de la celda del argumento ref.
"COLOR"	Valor 1 si la celda tiene formato de color para los valores negativos; de lo contrario, devuelve 0 (cero).
"CONTENIDO"	Valor de la celda superior izquierda de la referencia, no una fórmula.
"ARCHIVO"	Nombre del archivo (incluida la ruta de acceso completa) que contiene la referencia, en forma de texto. Devuelve texto vacío ("") si todavía no se ha guardado la hoja de cálculo que contiene la referencia.
"FORMATO"	Un valor de texto correspondiente al formato numérico de la celda. Los valores de texto para los distintos formatos se muestran en la siguiente tabla. Si la celda tiene formato de color para los números negativos, devuelve "-" al final del valor de texto. Si la celda está definida para mostrar todos los valores o los valores positivos entre paréntesis, devuelve "()" al final del valor de texto.
"PARENTESIS"	Valor 1 si la celda tiene formato con paréntesis para los valores positivos o para todos los valores; de lo contrario, devuelve 0 (cero).
"PREFIJO"	Un valor de texto que corresponde al "prefijo de rótulo" de la celda. Devuelve un apóstrofo (') si la celda contiene texto alineado a la izquierda, comillas (") si la celda contiene texto alineado a la derecha, un acento circunflejo (^) si el texto de la celda está centrado, una barra inversa (\) si la celda contiene texto con alineación de relleno y devolverá texto vacío ("") si la celda contiene otro valor.
"PROTEGER"	Valor 0 (cero) si la celda no está bloqueada; de lo contrario, devuelve 1 si la celda está bloqueada.
"FILA"	El número de fila de la celda del argumento ref.
"TIPO"	Un valor de texto que corresponde al tipo de datos de la celda. Devolverá "b" (para blanco) si la celda está vacía, "r" (para rótulo) si la celda contiene una constante de texto y "v" (para valor) si la celda contiene otro valor.
"ANCHO"	El ancho de columna de la celda redondeado a un entero. Cada unidad del ancho de columna es igual al ancho de un carácter en el tamaño de fuente predeterminado.

referencia: (argumento opcional) La celda sobre la que desea información. Si se omite, se devuelve la información especificada en el argumento tipo_de_info para la última celda cambiada. Si el argumento de referencia es un rango de celdas, la función CELDA devuelve la información sólo para la celda superior izquierda del rango.

He destacado en naranja "formato" porque es el tipo_de_info con el que vamos a trabajar. Para ello nos situamos por ejemplo en la celda E3 y escribimos la fórmula:

=CELDA("formato";B3)   y copiamos hasta E9:

Como se puede ver, en aquellas celdas que tenemos formato de fecha obtenemos la referencia D1. Haciendo uso de la ayuda de Excel, la siguiente lista describe los valores de texto que devuelve la función CELDA cuando el argumento tipo_de_info es "formato":

Si el formato de Excel es	La función CELDA devuelve
Estándar	"G"
0	"F0"
#.##0	".0"
0,00	"F2"
#.##0,00	".2"
$#,##0_);($#,##0)	"C0"
$#.##0;(rojo)-$#.##0	"-M0"
$#.##0,00_);($#.##0,00)	"C2"
$#.##0,00;(rojo)-$#.##0,00	"-M2"
0%	"P0"
0,00%	"P2"
0,00E+00	"C2"
# ?/? o # ??/??	"G"
d/m/aa o d/m/aa h:mm o dd/mm/aa	"D4"
d-mmm-aa o dd-mm-aa	"D1"
d-mmm	"D2"
mmm-aa	"D3"
mm/dd	"D5"
h:mm a.m./p.m.	"D7"
h:mm:ss a.m./p.m.	"D6"
h:mm	"D9"
h:mm:ss	"D8"

Como se puede comprobar, todos los formatos de fecha comienzan por la letra D. Por ello hacemos ahora la siguiente fórmula en la celda D3:

=SI(IZQUIERDA(E3;1)="D";"Sí";"No")   y copiamos hasta la celda D9:

Evidentemente, podríamos resolver el modelo con una única fórmula en D3 que nos evitaría la columna E, a saber:

=SI(IZQUIERDA(CELDA("formato";B3);1)="D";"Sí";"No")

IMPORTANTE: Si el argumento tipo_de_info de la función CELDA es "formato", como en nuestro caso, y procedemos a asignar un formato diferente al inicial a la celda a la que se hace referencia, es necesario volver a calcular la hoja de cálculo (o pulsar F9) para poder actualizar los resultados de dicha función.

Si queremos destacar en otro color aquellas entradas que son fechas entonces tenemos que hacer uso de la herramienta de Formato condicional. Para ello seleccionamos el rango B3:B9 y vamos a Formato condicional y formulamos como se detalla en la imagen a continuación:

Obtener Parte de una Cadena Alfanumérica

"Tengo una columna con más de 500 registros de un código alfanumérico. La estructura es: una serie de números, un espacio, una serie de letras. La cantidad de números y letras es variable pero siempre los separa un espacio. Necesito obtener sólo los números y que se queden como formato de valor (no de texto)".

La solución es muy sencilla utilizando tres funciones como VALOR, IZQUIERDA y HALLAR. Partimos del ejemplo que se muestra en la siguiente imagen y queremos obtenr lo que se muestra en la segunda imagen:

Anidando las tres funciones citadas podemos resolverlo en una sola fórmula pero empezaré detallando paso a paso para su mejor comprensión:

Lo primero es obtener la posición del espacio para cada código. Esto lo podemos hacer utilizando la función HALLAR. Esta función busca una cadena de texto dentro de una segunda cadena de texto y devuelven el número de la posición inicial de la primera cadena de texto desde el primer carácter de la segunda cadena de texto. Es muy similar a la función ENCONTRAR con la diferencia de que la función HALLAR no distingue en su búsqueda entre mayúsculas y minúsculas, mientras que la función ENCONTRAR sí lo hace. Nos situamos en la celda F4 y escribimos la fórmula:

=HALLAR(" ";B4)-1  Le estamos pidiendo que busque un espacio dentro del texto de B4. El resultado será 6 porque el espacio en blanco es el sexto carácter del código que se encuentra en la celda B4. Como además le restamos 1, el resultado será 5, que es, precisamente, el número de dígitos del primer código.

Ya tenemos el número de dígitos de todos los códigos de la columna B. A continuación tendremos que proceder a"extirparlos". Para ello nos situamos en la celda G4 y escribimos la siguiente fórmula:

=IZQUIERDA(B4;F4)   De esta manera obtendremos la parte numérica del código. Al tratarse de una función de texto, el resultado obtenido es un texto y no un valor como deseamos. Para solucionar esto procederemos con el último paso...

 Nos situasmos en la celda H4 y escribimos:

=VALOR(G4)   De esta manera convertimos los dígitos del código en valor (en vez de texto):

Como ya avancé, podemos resumir estos tres pasos anidando en una sola función que escribimos en  la celda D4:

=VALOR(IZQUIERDA(B4;HALLAR(" ";B4)-1))

Copiamos hacia abajo y trabajo terminado:

Para obtener la parte alfabética del código podemos utilizar la siguiente fórmula que escribimos en F4 y copiamos hacia abajo:

=DERECHA(B4;LARGO(B4)-HALLAR(" ";B4))

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")

Cambiar Formato (Fecha) Americano a Español

Descargar el Archivo

"Todas las semanas me llega un informe con diversos datos y en la primera columna me encuentro con fechas en formato americano y, además, con formato de texto. Me gustaría poder convertirlas a formato español y poder operar con ellas".

Vamos a solucionar este problema con las funciones FECHA, IZQUIERDA, DERECHA y EXTRAE. Supongamos que tenemos la siguiente tabla con fechas, que además nos llegan con formato texto, en formato americano (mes,día,año) y queremos convertirlas en formato español y numérico (día,mes,año):

Vamos a "trocear" el problema para que se entienda mejor la solución:

1. Nos situamos en C3 y escribimos la fórmula:

=EXTRAE(B3;4;2)

La función EXTRAE nos permite extraer parte de un texto desde una posición inicial que le debemos indicar. En nuestro caso le estamos pidiendo que del texto que hay en B3 y desde la posición inicial 4 (4 caracteres) extraiga 2 caracteres. De esta manera obtendremos los dos caracteres referente al día.

2. Nos situamos en la celda D3 y escribimos la siguiente fórmula:

=IZQUIERDA(B3;2)

Esta función extrae dos caracteres de la celda B3 empezando por la izquierda.

3. Nos situamos en E3 y escribimos la fórmula:

=DERECHA(B3;4)

Esta función extrae cuatro caracteres de la celda B3 empezando por la derecha.

Ya hemos conseguido separar los caracteres referentes al día, mes y año:

Sólo nos queda "reunirlos" con formato de fecha válido para poder operar después con ellas. Esta labor la realiza la función FECHA:

4. Nos situamos en la celda F3 y escribimos:

=FECHA(E3;D3;C3)

Seleccionando ahora el rango C3:F3 y haciendo doble clic en la parte inferior derecha de la selección terminaríamos nuestro trabajo:

Podemos resumir todos estos pasos en una sola fórmula, a saber:

=FECHA(DERECHA(B3;4);IZQUIERDA(B3;2);EXTRAE(B3;4;2))

Evitar datos repetidos con Validación Datos Personalizado

Supongamos que tenemos una tabla en la que vamos introduciendo los datos relativos a las ventas producidas en cada delegación de nuestra compañía semanalmente. La primera columna hace referencia al código de la operación. Dicho código es único, es decir, no se puede repetir, y representa la delegación donde se ha producido la venta (dos primeros dígitos) y el número de factura (tres últimos dígitos). Queremos que si por error intentamos introducir un código repetido la hoja no nos lo permita y nos avise con un mensaje. También queremos que en la tercera columna de la tabla nos indique, tras introducir el código, a qué delegación pertenece la venta en cuestión. Para todas estas labores utilizaremos Formato Condicional, Validación de Datos y las funciones CONTAR.SI, SI, O, BUSCARV y IZQUIERDA.

Lo primero que buscamos es lo que puede ver en la siguiente imagen: que no nos permita introducir datos repetidos en el campo Código.

1. Seleccionamos el rango donde vayamos a introducir los datos, en nuestro caso A4:A20 y vamos al menú Datos/Validación de datos.

2. Seleccionamos Permitir/Personalizada y en el recuadro Fórmula escribimos:

=CONTAR.SI($A$4:$A$20;A4)=1

3. Antes de aceptar pulsamos la pestaña de mensaje de error y personalizamos el mensaje que queramos que aparezca si intentamos duplicar un registro. Una vez hecho pulsamos Aceptar.

Gracias a esta sencilla fórmula cada registro (A4, A5, A6...) es comparado con el rango $A$4:$A$20. Como establecemos la condición de que al contar cada registro debe ser igual a 1 si intentamos volver a introducir el mismo registro nos saldrá el mensaje de error que le hayamos indicado.

Por otro lado nos interesa que en C4 y siguientes filas nos indique automáticamente en qué delegación se ha realizado la venta (utilizaremos esta información más adelante para generar un informe resumen por delegación). Para ello elaboramos la tabla que se muestra en la figura en el rango E3:F9.

4. Seleccionamos el rango E4:F9 y le damos el nombre de ciudades.

5. Para resolver este problema además de la función BUSCARV necesitaremos otra función que nos permita extraer del código los dos primeros dígitos. Dicha función será IZQUIERDA. Si escribiéramos =IZQUIERDA(A4;2) obtendríamos los dos primeros caracteres de la celda A4, que es precisamente lo que necesitamos para realizar después la búsqueda en nuestra tabla de ciudades. Así pues, nos situamos en la celda C4 y escribimos la siguiente fórmula:

=BUSCARV(IZQUIERDA(A4;2);Ciudades;2;Falso) De esta manera obtendremos la ciudad a la que hace referencia el código. Si queremos mejorar nuestra fórmula para que sólo aparezca la ciudad en caso de que introduzcamos un código en la columna A y un importe en la columna B deberemos añadir dos funciones lógicas, a saber, SI y O:

=SI(O(A4="";B4="");"";BUSCARV(IZQUIERDA(A4;2);Ciudades;2;Falso))