☰

如何将时间字符串转换为秒

tag-icon • tag-icon microsoft-excel
如何将时间字符串转换为秒

我有一个大约有 5000 行的 CSV 文件导入到 Excel 中，我的问题是我有多个类似的时间字符串1h1m1s，需要将它们转换为秒。

以下是几个例子：

A 列中的数据

0m11s         
2m32s        
3m10s        
1h2m35s

结果为 Col B

11 seconds
152 seconds  
190 seconds    
3755 seconds

我尝试格式化单元格但最终得到了一些奇怪的结果！

可以这样做吗？如果可以，那么我该如何将时间字符串转换为秒？

答案1

使用 VBA 来实现这个功能相对比较容易。

一种方法是使用正则表达式来解析字符串，然后将每个部分乘以适当的转换。

要输入此用户定义函数 (UDF)，alt-F11请打开 Visual Basic 编辑器。确保您的项目在 Project Explorer 窗口中突出显示。然后，从顶部菜单中，选择Insert/Module下面的代码并将其粘贴到打开的窗口中。

要使用此用户定义函数 (UDF)，请输入如下公式

=convSeconds(A1)

在某个牢房里。

这将仅返回秒数，如下所示。如果要附加单词Seconds，您可以将公式与字符串连接起来；或者使用自定义数字格式（这将保留结果的数字质量）。

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

在此处输入图片描述

以下是正则表达式的解释

提取小时/分钟/秒

(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?

创建于正则表达式好友

答案2

这是一个简单的方法。所有操作都可以在一个公式中完成，但我使用了一些辅助列来避免重复。

在此处输入图片描述

在您的示例中，至少有分钟和秒的占位符，但只有当有小时时才包括小时。第一步寻找“h”。B1 中的公式：

=FIND("h",A1)

如果没有“h”，则返回错误。否则，返回字符串中 h 的位置（假设小时值可能超过 9，否则您可以直接取左边的字符）。

C 列剥离了始终相同的部分。C1 中的公式：

=IF(ISERROR(B1),A1,MID(A1,B1+1,LEN(A1)))

如果没有找到“h”，则使用原始字符串，否则采用 h 之后的所有内容。

D 列定位“m”。D1 中的公式与 B1 中的公式类似：

=FIND("m",C1)

E 列使用这些部分来计算秒数。E1 中的公式：

=IF(ISERROR(B1),0,3600*LEFT(A1,B1-1))+LEFT(C1,D1-1)*60+MID(C1,D1+1,LEN(C1)-D1-1)

如果未找到“h”，则小时贡献为零，否则为“h”左侧数字的 3600 倍。分钟贡献为“m”左侧数字的 60 倍。秒贡献为“m”之后和最后一个字符（“s”）之前的数字。

您可以隐藏 B:D 列。如果您确实希望所有内容都包含在一个公式中，只需将对辅助单元格的引用替换为相关的辅助单元格公式即可。

注意事项：此公式处理问题中数据的特征，该数据始终包含分钟和秒值。对于不一定包含分钟或秒的情况，则需要更复杂的公式。

答案3

我假设输入数据位于 A 列第 1 行。

您可以尝试以下公式（复制 + 粘贴到 B1 并根据需要向下拖动）：

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

答案4

这个问题已经有了解决该问题中的问题的答案，其中包含始终包含分钟和秒的简化。人们可能会遇到类似但更普遍的问题，即仅包含所需的时间组件，因此可能缺少任何一个或多个组件。

Ron Rosenfeld 的出色回答已经解决了这个问题。对于不熟悉 VBA 或正则表达式的读者，他们可能更喜欢基于公式的解决方案，因为他们可以更轻松地适应和维护，而其他当前答案无法处理一般情况。我将把这个基于公式的解决方案作为单独的答案发布，因为它比这个问题中有限情况所需的更复杂，将它与我的其他答案结合起来会使这两种解决方案都更难理解。

在此处输入图片描述

此解决方案还使用了一些辅助列来避免公式重复，但采用了略有不同的方法。B 列至 D 列分别查找不同的时间组件字母。第一行中的公式：

B1:  =FIND("h",A1)
C1:  =FIND("m",A1)
D1:  =FIND("s",A1)

如果字母存在，则返回该字母在原始字符串中的位置，否则返回错误。如果存在“s”，则始终是最后一个字符，但找到其字符位置可以简化提取秒值的过程，而 FIND 则具有双重目的，即识别它是否存在。

E 列提取每个时间分量值，将其乘以秒的转换系数，然后将它们相加。它使用 MID 根据 B 至 D 列中找到的标记位置提取值。

复杂之处在于，对于分钟和秒，前面的部分或全部时间分量可能不存在；公式需要根据前面的分量是否存在来确定当前分量在字符串中的开始位置。E1 中的公式为：

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

为了便于阅读，我在公式的时间部分之间添加了换行符，因此请删除它们以复制和粘贴公式。

Hours 非常简单。它取“h”前面左侧的字符（如果缺少“h”，则取零），然后将其乘以 3600。

分钟值前面只能是小时。如果存在分钟值，则根据是否存在小时部分来计算 MID 的起点和长度。

如果存在秒值，则秒值的位置取决于小时和分钟组件之一或两者的存在。如果存在“m”，则其位置在字符串中的位置将始终晚于“h”（如果存在）的位置。如果不存在任何一个，则公式将使用该组件的零值。然后，MAX 函数提供原始字符串中秒值之前的最远位置，并确定字符串的长度中有多少被先前的时间组件占用。

辅助列可以隐藏。它们可以合并到 E 列的公式中，但如果您查看这些值的使用次数，就会发现生成的公式会非常长且难以维护。

相关内容