Máximo de un Alfanumérico

"Tengo un listado en el que llevo el seguimiento de varias ordenes. Todas ellas están compuestas por un código único alfanumérico de 7 caracteres. Los tres primeros son siempre el texto GIO y los otros cuatro son números. Necesito hallar el código más alto en función de su número".

Partimos del siguiente ejemplo:

Al tratarse de entradas alfanuméricas (texto y números) excel las considera texto y, en consecuencia, no podemos utilizar directamente la función MAX. Podemos resolver el problema de diferentes maneras. Una muy sencilla es "trocear" las entradas para separar la parte de texto de la de número. Para ello generamos una columna de proceso:

En la celda D6 escribimos la fórmula:

=VALOR(DERECHA(H6;4))   y la copiamos hasta D23.

De esta manera estamos obteniendo los 4 dígitos con la función DERECHA, y convirtiendo dichos dígitos, que hasta aquí excel trata como texto, a números con la función VALOR: 

Nos situamos ahora en la celda B3 y escribimos la siguiente fórmula:

Lo que estamos haciendo es CONCATENAR el texto "GIO", con el que comienzan todos los códigos del listado, con el valor MÁXIMO  de los números:

Podemos concluir aplicando Formato Condicional al rango B6:B23 para que destaque el máximo valor, como se muestra en la imagen.

Dígitos Duplicados en Distinto Orden

"Tengo un listado de valores de tres dígitos y necesito detectar cuáles están duplicados en el mismo o distinto orden. Por ejemplo 123, 231, 456, 456, 564, etcétera". 

Partimos del siguiente ejemplo:

Preparamos ahora las siguientes tablas para "manipular" los datos iniciales. Aunque el enunciado habla de 3 dígitos, preparamos tablas para contemplar hasta 6 dígitos:

Nos situamos en E3 y escribimos la fórmula(que copiaremos hasta J3 y, finalmente, hasta J17):

=SI.ERROR(VALOR(EXTRAE($B3;E$2;1));"")

De esta manera estamos extrayendo cada dígito y reconvirtiéndolo en VALOR (ya que la función EXTRAE nos lo devuelve como texto):

 Una vez hecho esto, nos situamos en L3 y escribimos la fórmula:

=SI.ERROR(K.ESIMO.MENOR($E3:$J3;L$2);"")

y copiamos hasta Q2 y finalmente hasta Q17:

De esta manera hemos reordenado de menor a mayor los dígitos. Procedemos ahora a unirlos de nuevo con la función CONCATENAR (&). En S3 escribimos:

y copiamos hasta S17:

Ahora ya podemos proceder a contar los números que se repiten en más de una ocasión y "asociarlos" con sus "originales". Nos situamos en la celda C3 y escribimos la fórmula:

=SI(CONTAR.SI($S$3:$S$17;S3)>1;"Repetido";"")    y copiamos hasta C17:

Detectar Códigos Alfanuméricos Pares

"Tengo más de 500 entradas en una columna de un código compuesto de letras y, al final, 4 dígitos. Necesito localizar cuáles de esos códigos son pares y que me escriba en una columna anexa dichos dígitos (sólo los que son pares)".

Antes de meterme en materia me permitiréis que siendo hoy el día que es os felicite a todos, primero por tener la paciencia de leerme de vez en cuando y, segundo y sobre todo, porque hoy sea un día que podáis disfrutar en familia y no dejéis que os lo estropee nadie (ni siquiera los políticos con o sin coleta...)

Partimos del siguiente ejemplo: (ups! Se me ha "colao" un señor de barba blanca en el ejemplo y haciendo publicidad para mi hermano Santi...)

Nos situamos en D3 y escribimos la siguiente fórmula que copiamos hasta D15 y que paso a desmenuzar a continuación:

=SI(N(ES.PAR(VALOR(DERECHA(B3;1))))=0;"";VALOR(DERECHA(B3;4)))

DERECHA(B3;1)   esta parte de la fórmula extrae 1 dígito empezando por la derecha del texto existente en B3. Aunque se trata "visualmente" de un número, excel lo trata como texto por formar parte precisamente de una cadena de texto. Para convertirlo en número utilizamos la función VALOR, a saber: VALOR(DERECHA(B3;1)).

Una vez hecho esto, procedemos a comprobar si el dígito que acabamos de extraer es par o no. Para ello utilizamos la función ES.PAR, ES.PAR(VALOR(DERECHA(B3;1)))  que nos devolverá el resultado VERDADERO o FALSO. Para convertir este VERDADERO ó FALSO en 1 ó 0 utilizamos la función N (también podríamos poner dos signos negativos consecutivos -- en vez de dicha función)  N(ES.PAR(VALOR(DERECHA(B3;1)))).

Ya sólo nos queda anidar esta fórmula dentro de un condicional para que si el último dígito no es un número par (y por lo tanto la fórmula N(ES.PAR(VALOR(DERECHA(B3;1)))) será igual a 0) no escriba nada o, en caso contrario, que escriba los 4 dígitos del código como valor:  VALOR(DERECHA(B3;4)).

El resultado final es el que se muestra a continuación:

Feliz Navidad a todos y recordad: Para ser feliz hay que venir a pasar la Navidad al Balneario de Mondariz!!

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

Personalizando Fórmulas con Validación de Datos

"He leído el anterior artículo y me gustaría saber si hay forma de limitar la entrada de datos, por medio de la validación de datos, para conseguir que no permita introducir un código en la celda si no cumple la siguientes reglas: a)que el número total de caracteres sea de siete; b) que los dos primeros caracteres sean dos letras y estén escritos en mayúscula; c) que los siguientes caracteres sean 5 números."

Lo que queremos conseguir requiere del uso de la herramienta Validación de Datos por un lado y del uso de unas cuantas funciones para garantizar que se cumplen todas las restricciones indicadas por nuestro lector. En concreto, las restricciones que debe contemplar la fórmula que realicemos son:

1. Que el número de caracteres totales introducidos ha de ser 7

2. Que los dos primeros caracteres han de ser texto

3. Que los dos primeros caracteres han de ser mayúsculas

4. Que los caracteres del 3 al 7 han de ser números

Supongamos que tenemos una zona de introducción de datos como la mostrada en la siguiente imagen:

Lo que debemos hacer es seleccionar el rango B4:B12 que es nuestra zona de entrada de datos. Vamos a la ficha Datos/Validación/Validación de Datos y seleccionamos Permitir Personalizada. En Fórmula escribimos la siguiente (es un poco larga):

=Y(LARGO(B4)=7;IGUAL(B4;MAYUSC(B4));ESERROR(VALOR(EXTRAE(B4;1;1)));

ESERROR(VALOR(EXTRAE(B4;2;1)));

ESERROR(VALOR(EXTRAE(B4;3;5)))=FALSO)=VERDADERO

Voy a explicar las distintas funciones y partes de la fórmula. Empezamos con la función Y. Esta función nos sirve para comprobar si se cumplen una serie de pruebas que vamos a realizar. En caso de que se cumplan todas las pruebas que realicemos el valor que nos devolverá esta función será VERDADERO y en caso contrario FALSO (precisamente por este motivo podemos utilizarla dentro de validación de datos como fórmula personalizada).

La primera prueba que realizamos es la de el número total de dígitos. Para ello hacemos uso de la función LARGO, que nos devuelve el número de caracteres existentes en una celda, y comprobamos si es igual a 7.

La segunda comprobación que realizamos es si los dos primeros caracteres son mayúsculas. Para ello utilizamos la función IGUAL y la función MAYUSC. La función IGUAL comprueba si dos cadenas de texto son idénticas o no diferenciando entre mayúsculas y minúsculas.

Ahora comprobamos si los dos primeros caracteres son letras. Para ello debemos extraer dichos 2 caracteres para analizarlos. Hacemos uso de la función EXTRAE. Al utilizar esta función excel considera como texto los caracteres extraídos. En caso de que se trate de números será sencillo convertirlos nuevamente haciendo uso de la función VALOR. Finalmente utilizo la función ESERROR para comprobar que si tras convertir los dos primeros caracteres a número me devuelve un mensaje de error sólo puede ser debido a que se trate de texto. La misma operación pero al revés, es decir, comprobando que el valor devuelto es FALSO, resolverá la última parte de la fórmula donde verifico que los últimos 5 dígitos son números.

Una vez introducida esta fórmula en la validación de datos ya puede comprobar que el funcionamiento es el deseado y que sólo nos permitirá introducir valores correctos. Cada vez que cometamos un error nos aparecerá el mensaje que definamos dentro de la validación de datos, tal y como se muestra en los siguientes ejemplos: