Extraer horas

Jaime Franco Jimenez
14 feb 2024
7 Min. de lectura

Para el siguiente ejemplo tenemos una serie de cadenas, donde vemos concatenada diferentes horas.

Debemos de separar cada hora, u, horas de cada cadena, una hora puede estar compuesta de cinco dígitos, por ejemplo, 12:43, o, 4:5, o, 8:14.

La primera hora esta bien, la segunda hora, esta mal, porque los minutos deben de estar formado por dos dígitos, la tercera esta bien, pero, la hora en vez de estar formada por dos dígitos, esta formada por un digito, además, debemos de excluir aquellas horas que sean superiores a 24, o, que los minutos sean mayores a 60.

En la celda C2, usamos LET, creamos una variable, usamos la funcion EXTRAE, como argumento texto, seleccionamos la celda A2, como argumento posición inicial, usamos la funcion SECUENCIA, como argumento filas, usamos la funcion LARGO, y, como argumento ponemos la celda A2, como argumento numero de caracteres, ponemos 1, cerramos paréntesis, y, probamos variable.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);a)

Tenemos extraído cada carácter de la celda A2.

Vamos a excluir las letras mayúsculas, las letras minúsculas, el carácter dólar, y, el carácter guion bajo.

Creamos otra variable, usamos la funcion BYROW, para que trabaje con cada fila de forma independiente, como argumento array, ponemos la variable “a”, como argumento funcion, ponemos LAMBDA, y, declaramos una variable.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);b;BYROW(a;LAMBDA(x

Como argumento calculo de LAMBDA, ponemos el operador lógico O, como argumento valor logico1, usamos el operador lógico Y, como argumento valor logico1, usamos la funcion CODIGO, como argumento ponemos la variable “x”, comparamos con mayor o igual a 65, el numero 65 corresponde a la letra “A”, como argumento valor logico2, volvemos a usar la funcion CODIGO aplicado a la variable “x”, y, comparamos con menor o igual a 90, el código 90 corresponde a la letra “Z”, es decir, vamos a comparar si el valor de la variable “x” esta entre 65 y 90, es decir, es una letra mayúscula, cerramos paréntesis.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90)

Como argumento valor logico2 del operador lógico O, volvemos a usar el operador lógico Y, y, comparamos el código de la variable “x” con mayor o igual a 97, y, menor o igual a 122, es decir, es una letra minúscula.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122)

Como argumento valor logico3 del operador lógico O, comparamos el código de la variable “x” con 36, que corresponde con el código de dólar, como argumento valor logico4, comparamos el código de la variable “x” con el valor 96, que es el código del guion bajo.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122);CODIGO(x)=36;CODIGO(x)=95)))

Tenemos una matriz desbordada en vertical con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.

Nos interesan los valores FALSOS porque corresponden a una hora, creamos otra variable, usamos la funcion FILTRAR, como argumento array, ponemos la variable “a”, como argumento Include, ponemos la variable “b” e igualamos a FALSO.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122);CODIGO(x)=36;CODIGO(x)=95)));c;FILTRAR(a;b=FALSO);c)

Tenemos solo las horas.

Vamos a cambiar la celda A2 de la variable “a” por la celda A10, que tenemos una hora formada por un solo digito.

=LET(a;EXTRAE(A10;SECUENCIA(LARGO(A10));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122);CODIGO(x)=36;CODIGO(x)=95)));c;FILTRAR(a;b=FALSO);c)

Lo normal es pensar que, desde los dos puntos, dos celdas hacia arriba, tenemos la hora, y, desde los dos puntos, dos celdas hacia abajo, tenemos los minutos, pero, en este caso, esta condición no se cumple, porque la ultima hora, esta compuesta de un solo digito.

Creamos otra variable, usamos la funcion HALLAR, como argumento texto buscado, entre comillas dobles ponemos dos puntos, como argumento dentro del texto, es la funcion FILTRAR.

=LET(a;EXTRAE(A4;SECUENCIA(LARGO(A4));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122);CODIGO(x)=36;CODIGO(x)=95)));c;FILTRAR(a;b=FALSO);d;HALLAR(":";c);d)

Tenemos una matriz desbordada en vertical donde tenemos error donde no hay dos puntos, y, la posición de los dos puntos donde los haya.

Creamos otra variable, usamos la funcion SECUENCIA, como argumento filas, ponemos la funcion FILAS, como argumento matriz, ponemos la variable “c”.

Tenemos una matriz desbordada desde el numero 1 hasta el número máximo de filas.

Creamos otra variable, usamos el condicional SI, como argumento prueba lógica, preguntamos si es numero la variable “c”, como argumento valor si verdadero, ponemos la variable “e”, como argumento valor si falso, ponemos un error.

Tenemos otra matriz desbordada con error donde no hay coincidencia, y, la posición dentro de las horas donde se encuentran los dos puntos.

Usamos la funcion ENCOL antes del condicional SI, como argumento matriz es el condicional SI, como argumento ignorar, seleccionamos 3, ignorar blancos y errores.

Tenemos los números, o, posición donde se encuentran los dos puntos.

Creamos otra variable, usamos el condicional SI, como argumento prueba lógica, usamos la funcion ESERROR, como argumento valor, ponemos la variable “d”, como argumento valor si verdadero, ponemos la variable “e”, como argumento valor si falso, ponemos blanco.

Tenemos una matriz desbordada con los números donde había error, y, blanco donde no había error.

Creamos otra variable, usamos la funcion UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma, incluimos las celdas vacías, como argumento texto1, ponemos la variable “g”.

En una celda, tenemos los números separados por comas, pero, si nos fijamos, vemos que donde iba dos puntos, o, un carácter como dólar, o, guion bajo, aparecen dos comas.

Creamos otra variable, usamos la funcion DIVIDIRTEXTO, como argumento texto, ponemos la variable “h”, como argumento delimitador de columna, entre comillas, ponemos dos comas.

Tenemos una matriz desbordada en horizontal donde en cada celda tenemos los números que hay antes, y, después del delimitador.

¿Por qué estamos haciendo esto?

Ya hemos visto que hay horas compuesta de un digito, cosa que debemos de tener en cuenta a la hora de extraer, con lo que vamos a realizar ahora, es para detectar dichas horas.

Creamos otra variable, usamos la funcion REDUCE, como argumento valor inicial, ponemos blanco, como argumento array, ponemos la variable “i”, como argumento funcion, ponemos LAMBDA, y, declaramos dos variables.

Como argumento calculo de LAMBDA, usamos la funcion APILARV, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, usamos la funcion DIVIDIRTEXTO, como argumento texto, ponemos la variable “y”, como argumento delimitador de columna, entre comillas dobles, ponemos coma.

Tenemos una matriz desbordada de cuatro columnas, donde la primera fila está de más, después tenemos dividido en filas cada valor que teníamos del resultado de la variable “i”.

Usamos la funcion EXCLUIR para quitar la primera fila, la usamos antes de REDUCE, como argumento matriz es la funcion REDUCE, como argumento filas, ponemos 1, omitimos el argumento columnas.

Vemos que los números quedan alineados a la izquierda, quiere decir que están en formato de texto, pues, multiplicamos la expresión de la variable “j” por 1.

Ya tenemos los números alineados a la derecha, quiere decir que están en formato de número.

Usamos la funcion SI.ERROR, y, ponemos blanco donde hay error.

Creamos otra variable, usamos la funcion BYROW, como argumento array, ponemos la variable “j”, como argumento funcion, ponemos LAMBDA, creamos una variable, como argumento calculo de LAMBDA, usamos la funcion CONTAR, y, como argumento ponemos la variable “x”.

Tenemos una matriz desbordada en vertical con el recuento de cantidades de cada columna.

Donde aparece el valor 3, quiere decir, que la hora sin contar los dos puntos es de tres dígitos, quiere decir que la hora esta formada por un digito.

Se trata de que, a partir de los dos puntos, nos vamos a mover hacia arriba, y, hacia abajo, la pregunta es ¿cuánto tenemos que movernos hacia arriba?, si la hora esta formada por dos dígitos debemos de movernos dos celdas hacia arriba, pero, si esta formada por un digito, debemos de movernos una celda hacia arriba.

La última celda siempre está de más, creamos otra variable, usamos EXCLUIR, como argumento matriz, sumamos las variables “f”, y, “k”, como argumento filas, ponemos -1, omitimos el argumento columnas.

=LET(a;EXTRAE(A10;SECUENCIA(LARGO(A10));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122);CODIGO(x)=36;CODIGO(x)=95)));c;FILTRAR(a;b=FALSO);d;HALLAR(":";c);e;SECUENCIA(FILAS(c ));f;ENCOL(SI(ESNUMERO(d);e;NOD());3);g;SI(ESERROR(d);e;"");h;UNIRCADENAS(",";FALSO;g);i;DIVIDIRTEXTO(h;",,");j;SI.ERROR(EXCLUIR(SI.ERROR(REDUCE("";i;LAMBDA(x;y;APILARV(x;DIVIDIRTEXTO(y;","))));"");1)*1;"");k;SI(BYROW(j;LAMBDA(x;CONTAR(x)))<>3;-2;-1);l;EXCLUIR(f+k;-1);l)

Tenemos la posición donde debemos de empezar a extraer.

Creamos otra variable, usamos la funcion REDUCE, como argumento valor inicial, ponemos blanco, como argumento array, ponemos la variable “l”, como argumento funcion, ponemos LAMBDA, y, declaramos dos variables.

Como argumento calculo de LAMBDA, usamos la función APILARV, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, usamos la funcion SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos 5, que son las filas para movernos, como argumento inicio, ponemos la variable “y”.

Tenemos las posiciones a movernos, vemos que en la primera fila tenemos una secuencia de 5 números, desde el numero 1 al numero 5, cuando hayamos extraído dichas posiciones, tendremos la primera hora.

La primera fila, está de más, volvemos a usar la funcion EXCLUIR.

Creamos otra variable, usamos la funcion INDICE, como argumento matriz, ponemos la variable “c”, como argumento numero de fila, ponemos la variable “m”.

Hemos extraído las horas.

Como para la ultima hora, hemos extraído más caracteres de la cuenta, tenemos un error.

Quitamos el error con la funcion SI.ERROR, y, ponemos blanco.

Antes de SI.ERROR, ponemos la funcion BYROW, como argumento array, es la función SI.ERROR, como argumento funcion, ponemos LAMBDA, declaramos una variable, usamos la funcion UNIRCADENAS, como argumento delimitador, ponemos dobles comillas dobles, ignoramos celdas vacías, como argumento texto1, ponemos la variable “x”.

Ya hemos extraído las horas.

Antes de BYROW, usamos la funcion ENFILA.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122);CODIGO(x)=36;CODIGO(x)=95)));c;FILTRAR(a;b=FALSO);d;HALLAR(":";c);e;SECUENCIA(FILAS(c ));f;ENCOL(SI(ESNUMERO(d);e;NOD());3);g;SI(ESERROR(d);e;"");h;UNIRCADENAS(",";FALSO;g);i;DIVIDIRTEXTO(h;",,");j;SI.ERROR(EXCLUIR(SI.ERROR(REDUCE("";i;LAMBDA(x;y;APILARV(x;DIVIDIRTEXTO(y;","))));"");1)*1;"");k;SI(BYROW(j;LAMBDA(x;CONTAR(x)))<>3;-2;-1);l;EXCLUIR(f+k;-1);m;EXCLUIR(REDUCE("";l;LAMBDA(x;y;APILARV(x;SECUENCIA(;5;y))));1);n;ENFILA(BYROW(SI.ERROR(INDICE(c;m);"");LAMBDA(x;UNIRCADENAS("";VERDADERO;x))));n)

Tenemos las horas en horizontal, podemos ver como la ultima hora que es de un digito, aparece correctamente.

Vamos a de nuevo a la expresión de la variable “a”, y, cambiamos A10 por A2.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122);CODIGO(x)=36;CODIGO(x)=95)));c;FILTRAR(a;b=FALSO);d;HALLAR(":";c);e;SECUENCIA(FILAS(c ));f;ENCOL(SI(ESNUMERO(d);e;NOD());3);g;SI(ESERROR(d);e;"");h;UNIRCADENAS(",";FALSO;g);i;DIVIDIRTEXTO(h;",,");j;SI.ERROR(EXCLUIR(SI.ERROR(REDUCE("";i;LAMBDA(x;y;APILARV(x;DIVIDIRTEXTO(y;","))));"");1)*1;"");k;SI(BYROW(j;LAMBDA(x;CONTAR(x)))<>3;-2;-1);l;EXCLUIR(f+k;-1);m;EXCLUIR(REDUCE("";l;LAMBDA(x;y;APILARV(x;SECUENCIA(;5;y))));1);n;ENFILA(BYROW(SI.ERROR(INDICE(c;m);"");LAMBDA(x;UNIRCADENAS("";VERDADERO;x))));n)

Arrastramos, y, tenemos extraído correctamente todas las horas.

¿Qué nos falta?

Aun no hemos controlado que, si una hora es mayor a 24, o, que los segundos sean mayores a 60, no deben de aparecer.

Creamos otra variable, usamos el condicional SI, como argumento prueba lógica, usamos la funcion TEXTOANTES, como argumento texto, ponemos la variable “n”, como argumento delimitador, entre comillas dobles, ponemos dos puntos, y, multiplicamos por 1, comparamos con mayor a 24.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122);CODIGO(x)=36;CODIGO(x)=95)));c;FILTRAR(a;b=FALSO);d;HALLAR(":";c);e;SECUENCIA(FILAS(c ));f;ENCOL(SI(ESNUMERO(d);e;NOD());3);g;SI(ESERROR(d);e;"");h;UNIRCADENAS(",";FALSO;g);i;DIVIDIRTEXTO(h;",,");j;SI.ERROR(EXCLUIR(SI.ERROR(REDUCE("";i;LAMBDA(x;y;APILARV(x;DIVIDIRTEXTO(y;","))));"");1)*1;"");k;SI(BYROW(j;LAMBDA(x;CONTAR(x)))<>3;-2;-1);l;EXCLUIR(f+k;-1);m;EXCLUIR(REDUCE("";l;LAMBDA(x;y;APILARV(x;SECUENCIA(;5;y))));1);n;ENFILA(BYROW(SI.ERROR(INDICE(c;m);"");LAMBDA(x;UNIRCADENAS("";VERDADERO;x))));o;SI(TEXTOANTES(n;":")*1>24

Como argumento valor si verdadero, ponemos blanco, como argumento valor si falso, ponemos otro condicional SI, como argumento prueba lógica, usamos la funcion TEXTODESPUES, como argumento texto, ponemos la variable “n”, como argumento delimitador, entre comillas dobles, ponemos dos puntos, multiplicamos por 1, y, comparamos con mayor a 60.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122);CODIGO(x)=36;CODIGO(x)=95)));c;FILTRAR(a;b=FALSO);d;HALLAR(":";c);e;SECUENCIA(FILAS(c ));f;ENCOL(SI(ESNUMERO(d);e;NOD());3);g;SI(ESERROR(d);e;"");h;UNIRCADENAS(",";FALSO;g);i;DIVIDIRTEXTO(h;",,");j;SI.ERROR(EXCLUIR(SI.ERROR(REDUCE("";i;LAMBDA(x;y;APILARV(x;DIVIDIRTEXTO(y;","))));"");1)*1;"");k;SI(BYROW(j;LAMBDA(x;CONTAR(x)))<>3;-2;-1);l;EXCLUIR(f+k;-1);m;EXCLUIR(REDUCE("";l;LAMBDA(x;y;APILARV(x;SECUENCIA(;5;y))));1);n;ENFILA(BYROW(SI.ERROR(INDICE(c;m);"");LAMBDA(x;UNIRCADENAS("";VERDADERO;x))));o;SI(TEXTOANTES(n;":")*1>24;"";SI(TEXTODESPUES(n;":")*1>60

Como argumento valor si verdadero del segundo condicional, ponemos blanco, como argumento valor si falso, ponemos otro condicional SI, usamos la funcion LARGO, como argumento ponemos la funcion TEXTODESPUES, como argumento texto, ponemos la variable “n”, como argumento delimitador, entre comillas dobles, ponemos dos puntos, cerramos paréntesis de TEXTODESPUES, y, de LARGO, igualamos a 1, como argumento valor si verdadero de este tercer condicional, ponemos blanco, como argumento valor si falso, poneos la variable “n”.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122);CODIGO(x)=36;CODIGO(x)=95)));c;FILTRAR(a;b=FALSO);d;HALLAR(":";c);e;SECUENCIA(FILAS(c ));f;ENCOL(SI(ESNUMERO(d);e;NOD());3);g;SI(ESERROR(d);e;"");h;UNIRCADENAS(",";FALSO;g);i;DIVIDIRTEXTO(h;",,");j;SI.ERROR(EXCLUIR(SI.ERROR(REDUCE("";i;LAMBDA(x;y;APILARV(x;DIVIDIRTEXTO(y;","))));"");1)*1;"");k;SI(BYROW(j;LAMBDA(x;CONTAR(x)))<>3;-2;-1);l;EXCLUIR(f+k;-1);m;EXCLUIR(REDUCE("";l;LAMBDA(x;y;APILARV(x;SECUENCIA(;5;y))));1);n;ENFILA(BYROW(SI.ERROR(INDICE(c;m);"");LAMBDA(x;UNIRCADENAS("";VERDADERO;x))));o;SI(TEXTOANTES(n;":")*1>24;"";SI(TEXTODESPUES(n;":")*1>60;"";SI(LARGO(TEXTODESPUES(n;":"))=1;"";n)));o)

Aceptamos, arrastramos y ya lo tenemos.

Usamos el argumento calculo de LET, ponemos BYROW, como argumento array, ponemos la variable “o”, como argumento funcion, ponemos LAMBDA, creamos una variable, usamos UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma, ignoramos celdas vacías, como argumento texto1, ponemos la variable x.

=LET(a;EXTRAE(A2;SECUENCIA(LARGO(A2));1);b;BYROW(a;LAMBDA(x;O(Y(CODIGO(x)>=65;CODIGO(x)<=90);Y(CODIGO(x)>=97;CODIGO(x)<=122);CODIGO(x)=36;CODIGO(x)=95)));c;FILTRAR(a;b=FALSO);d;HALLAR(":";c);e;SECUENCIA(FILAS(c ));f;ENCOL(SI(ESNUMERO(d);e;NOD());3);g;SI(ESERROR(d);e;"");h;UNIRCADENAS(",";FALSO;g);i;DIVIDIRTEXTO(h;",,");j;SI.ERROR(EXCLUIR(SI.ERROR(REDUCE("";i;LAMBDA(x;y;APILARV(x;DIVIDIRTEXTO(y;","))));"");1)*1;"");k;SI(BYROW(j;LAMBDA(x;CONTAR(x)))<>3;-2;-1);l;EXCLUIR(f+k;-1);m;EXCLUIR(REDUCE("";l;LAMBDA(x;y;APILARV(x;SECUENCIA(;5;y))));1);n;ENFILA(BYROW(SI.ERROR(INDICE(c;m);"");LAMBDA(x;UNIRCADENAS("";VERDADERO;x))));o;SI(TEXTOANTES(n;":")*1>24;"";SI(TEXTODESPUES(n;":")*1>60;"";SI(LARGO(TEXTODESPUES(n;":"))=1;"";n)));BYROW(o;LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))))

Aceptamos, arrastramos, y, ya tenemos nuestro ejercicio resuelto.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Extraer horas

Entradas recientes

Comentarios