Tengo un archivo CSV con alrededor de 5000 líneas importadas a Excel, y mi problema es que tengo varias cadenas de tiempo 1h1m1sy necesito convertirlas en segundos.
Aquí hay algunos ejemplos:
Datos en la Col A
0m11s
2m32s
3m10s
1h2m35s
Resultado en la Col B
11 seconds
152 seconds
190 seconds
3755 seconds
¡Intenté formatear celdas pero terminé con algunos resultados extraños!
¿Es posible hacerlo? En caso afirmativo, ¿cómo podría convertir la cadena de tiempo en segundos?
Respuesta1
Esto es relativamente fácil usando VBA.
Una forma es utilizar expresiones regulares para analizar la cadena y luego multiplicar cada sección por la conversión adecuada.
Para ingresar a esta función definida por el usuario (UDF), alt-F11abre el Editor de Visual Basic. Asegúrese de que su proyecto esté resaltado en la ventana del Explorador de proyectos. Luego, desde el menú superior, seleccione Insert/Moduley pegue el código a continuación en la ventana que se abre.
Para utilizar esta función definida por el usuario (UDF), ingrese una fórmula como
=convSeconds(A1)
en alguna celda.
Esto simplemente devolverá la cantidad de segundos, como se muestra a continuación. Si desea agregar la palabra Seconds, puede concatenar la fórmula con la cadena; o utilice un formato de número personalizado (que conservará la calidad numérica del resultado).
Option Explicit
Function convSeconds(s As String) As Long
Dim RE As Object, MC As Object
Dim SEC As Long
Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.ignorecase = True
.Pattern = "(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?"
If .test(s) = True Then
Set MC = .Execute(s)
With MC(0)
SEC = SEC + .submatches(0) * 3600 'hours
SEC = SEC + .submatches(1) * 60 'minutes
SEC = SEC + .submatches(2) 'seconds
End With
End If
End With
convSeconds = SEC
End Function
Aquí hay una explicación de la expresión regular.
Extraer h/m/s
(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?
- Coincide con la expresión regular a continuación
(?:(\d+)h)? - Coincide con la expresión regular a continuación
(?:(\d+)m)? - Coincide con la expresión regular a continuación
(?:(\d+)s)?
Creado conRegexBuddy
Respuesta2
Aquí tienes una forma sencilla de hacer esto. Todo se podría hacer en una fórmula, pero he usado algunas columnas auxiliares para evitar repeticiones.
En sus ejemplos, siempre hay al menos marcadores de posición para minutos y segundos, pero las horas se incluyen solo si hay horas. El primer paso busca una "h". Fórmula en B1:
=FIND("h",A1)
Si no hay "h", devuelve un error. De lo contrario, devuelve la posición de h en la cadena (asumiendo que el valor de la hora podría exceder 9; de lo contrario, podría simplemente tomar el carácter de la izquierda).
La columna C despega la parte que siempre será la misma. La fórmula en C1:
=IF(ISERROR(B1),A1,MID(A1,B1+1,LEN(A1)))
Si no se encontró ninguna "h", usa la cadena original; de lo contrario, toma todo lo que está después de la h.
La columna D ubica la "m". La fórmula en D1 es similar a la de B1:
=FIND("m",C1)
La columna E usa las piezas para calcular los segundos. La fórmula en E1:
=IF(ISERROR(B1),0,3600*LEFT(A1,B1-1))+LEFT(C1,D1-1)*60+MID(C1,D1+1,LEN(C1)-D1-1)
Si no se encontró ninguna "h", la contribución de horas es cero; de lo contrario, es 3600 veces lo que se encontró a la izquierda de la "h". El aporte de minutos es 60 veces lo encontrado a la izquierda de la "m". La contribución de segundos es el número que se encuentra después de la "m" y antes del último carácter (la "s").
Puede ocultar las columnas B:D. Si realmente desea tener todo en una sola fórmula, simplemente reemplace las referencias a las celdas auxiliares con la fórmula de celda auxiliar asociada.
Advertencia: esta fórmula trata de las características de los datos de la pregunta, que siempre contienen valores de minutos y segundos. Para una situación en la que los minutos o segundos no se incluyen necesariamente, se necesitaría una fórmula más complicada.
Respuesta3
Supongo que los datos de entrada se encuentran en la columna A, fila 1.
Puedes probar la siguiente fórmula (copiar y pegar en B1 y arrastrar hacia abajo según sea necesario):
=(IF(ISERROR(FIND("h",A1)),0,INT(MID(A1,1,FIND("h",A1)-1)))*3600)+(INT(IF(ISERROR(FIND("h",A1)),LEFT(A1,FIND("m",A1)-1),MID(A1,IF(ISERROR(FIND("h",A1)),0,FIND("h",A1)+1),FIND("m",A1)-FIND("h",A1)-1)))*60)+INT(MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1))
Respuesta4
Esta pregunta ya tiene respuestas que abordan el problema de la pregunta, que contiene la simplificación de que siempre se incluyen minutos y segundos. La gente puede llegar aquí con un problema similar, pero más general, de que sólo se incluyen los componentes de tiempo necesarios, por lo que es posible que falten uno o más de los componentes.
La gran respuesta de Ron Rosenfeld ya se ocupa de eso. Para los lectores que no están familiarizados con VBA o expresiones regulares, que podrían preferir una solución basada en fórmulas que puedan adaptar y mantener más fácilmente, las otras respuestas actuales no manejan el caso general. Publicaré esta solución basada en fórmulas como una respuesta separada porque es más complicada de lo requerido para el caso limitado de esta pregunta, y combinarla con mi otra respuesta haría que ambas soluciones sean menos accesibles.
Esta solución también utiliza algunas columnas auxiliares para evitar la repetición de fórmulas, pero adopta un enfoque ligeramente diferente. Cada una de las columnas B a D busca las diferentes letras del componente de tiempo. Las fórmulas de la primera fila:
B1: =FIND("h",A1)
C1: =FIND("m",A1)
D1: =FIND("s",A1)
Si la letra está presente, devuelve la posición de esa letra en la cadena original; de lo contrario, se produce un error. La "s", si está presente, siempre será el último carácter, pero encontrar la posición de su carácter simplifica la extracción del valor de los segundos, y FIND tiene el doble propósito de identificar si está presente.
La columna E extrae el valor de cada componente de tiempo, lo multiplica por el factor de conversión de segundos y los suma. Utiliza MID para extraer los valores según las ubicaciones de los marcadores que se encuentran en las columnas B a D.
La complejidad es que durante los minutos y segundos, algunos o todos los componentes de tiempo anteriores pueden no estar presentes; la fórmula necesita determinar en qué parte de la cadena comienza el componente actual en función de cuáles de los componentes anteriores están presentes. La fórmula en E1 es:
=3600*IFERROR(LEFT(A1,B1-1),0)+
60*IFERROR(MID(A1,1+IFERROR(B1,0),C1-1-IFERROR(B1,0)),0)+
IFERROR(MID(A1,1+MAX(IFERROR(B1,0),IFERROR(C1,0)),D1-1-MAX(IFERROR(B1,0),IFERROR(C1,0))),0)
Agregué saltos de línea a la fórmula entre los componentes de tiempo para facilitar la lectura, así que elimínelos para copiar y pegar la fórmula.
El horario es bastante simple. Toma los caracteres de la izquierda antes de la "h", o cero si faltaba la "h", y los multiplica por 3600.
El valor de los minutos sólo puede ir precedido de horas. Si hay un valor de minutos, calcula el punto de inicio y la duración de MID en función de si hay un componente de horas.
La ubicación del valor de los segundos, si está presente, depende de la presencia de uno o ambos componentes de horas y minutos. La ubicación de la "m", si está presente, siempre estará más adelante en la cadena que la ubicación de la "h", si está presente. Si alguno de ellos no está presente, la fórmula utiliza un valor de cero para ese componente. Luego, la función MAX proporciona la posición más lejana en la cadena original que precede al valor de segundos y determina qué parte de la longitud de la cadena está ocupada por componentes de tiempo anteriores.
Las columnas auxiliares se pueden ocultar. Podrían consolidarse en la fórmula de la columna E, pero si observa cuántas veces se usan esos valores, la fórmula resultante sería increíblemente larga y difícil de mantener.