excel - 将 Excel 文本转换为时间
问题描述
我必须将一些格式化文本转换为 excel 时间,但我自己找不到解决方案。
以下是输入文本的一些示例以及应如何转换:
- 1 Hour 14 minutes ==> 01:14:00
- 1 minute. ==> 00:01:00
- 1 Hour 1 minute ==> 01:01:00
- 2 minutes ==> 00:02:00
- 3 minutes 12 seconds ==> 00:03:12
- 29 seconds ==> 00:00:29
请注意,有些时候分和秒都有,而有些时候只有分/秒之一,除了有些时候你会找到分钟(复数),而有些时候只有分钟(单数)。最后,一些标点符号有时会出现在文本中。
数据位于电子表格列中,我想在电子表格的不同列中提取 excel 格式的时间。
我在一个单元格公式中尝试了不同版本的TimeValue()
andDateValue()
和一些嵌套的 replace() ,但没有一个适用于所有情况。
你能给我一个关于如何解决这个问题的想法或一些建议吗?
谢谢,
保罗
解决方案
我玩了你的问题一天,看到其他人也做了同样的事情。我很佩服 Ron Rosenfeld 提供的使用 FilterXML 的解决方案,我曾简单地认为它过于古怪而放弃了。它不是。但它并不整洁。对于“整洁”,请看下面的 UDF。从工作表中调用它,=TextToTime(A1)
并确保将您放入的单元格格式化为时间,也许像hh:mm:ss
.
Function TextToTime(Cell As Range) As Double
Dim Fun(2) As String
Dim Txt() As String
Dim Tmp As Variant
Dim i As Integer
Txt = Split(Cell.Value)
For i = 1 To UBound(Txt)
If Not IsNumeric(Txt(i)) Then
Tmp = 0
Tmp = InStr("HMS", UCase(Left(Trim(Txt(i)), 1)))
If Tmp Then
Fun(Tmp - 1) = Val(Txt(i - 1))
End If
End If
Next i
For i = LBound(Fun) To UBound(Fun)
If Fun(i) = "" Then Fun(i) = 0
Next i
TextToTime = TimeValue(Join(Fun, ":"))
End Function
这个功能非常通用。它可以翻译诸如“5 小时 10 分 15 秒”或“5 小时 10 分 15 秒”(带逗号)甚至“5 小时 10 分 15 秒”之类的字符串。它需要数字周围的空格,但不会因多余的空格或拼写错误而犹豫。它在“75 分 66 秒”时失败,但如果这是一个问题,可以处理。VBA 的主要优点是几乎任何事情都可以使用几行额外的代码来处理。工作表函数没有这种能力。
推荐阅读
- regex - 正则表达式删除两个标签之间的字符串的一部分
- android - Whatsapp 在 android 中共享音频文件时正在剪切音频文件
- render - A 帧渲染 lop 和视图刷新
- java - Groovy Shell 脚本对象未完全执行
- java - 从表 1 中获取用户列表,然后与表 2 中的数据进行比较
- angular - RXJS - 3 个并行的 observables,然后发出值并使用它来调用 3 个串联的 observables
- javascript - 如何访问 jquery 中的 css * 选择器?
- asp.net - 简单电子邮件服务中的 Web API 和电子邮件配置
- mysql - 无法从 cronjob 运行 smbclient 进行远程文件共享
- clickhouse - clickhouse分布式查询实现需要步骤