将Excel文本转换为时间 [英] Convert Excel Text to Time

问题描述

我必须将某些格式的文本转换为Excel时间，但我自己找不到解决方案.

I have to convert some formatted text to excel time, but I could not find a solution by myself.

以下是输入文本以及应如何转换的一些示例:

Here are some examples of the input text and how it should be converted:

 - 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

观察到有些时间既有分钟又有秒，而另一些时间只有几分之一秒，除了某些时候，您会发现分钟(复数)，而另一些则是分钟(单数).最后，有时标点符号可能会出现在文本中.

Observe that some times there are both minutes and seconds and some others only one of minutes/seconds, besides some times you find minutes (plural) and some others just minute (singular). Finally, some punctuation signs could be in the text some times.

数据在电子表格列中，我想在电子表格的另一列中提取excel格式的时间.

The data is in a spreadsheet column and I want to extract the excel formatted time in a different column on the spreadsheet.

我尝试了不同版本的 TimeValue()和 DateValue()以及一些嵌套的replace()，它们都在单元格公式中，但是它们都不起作用对于所有情况.

I've tried different versions of TimeValue() and DateValue() and some nested replace() all of them in a cell formula, but none of them worked for all cases.

您能给我一个有关解决此问题的想法或建议吗?

Could you give me an idea or some advice on how to approach this problem?

谢谢

保罗

推荐答案

我玩了一天您的问题，发现其他人也做了同样的事情.我很佩服Ron Rosenfeld使用FilterXML所提供的解决方案，我将其简短地认为并认为它太过荒唐了.不是.但是neitehr整洁吗?对于整洁"，只看下面的UDF.从工作表中使用诸如 = TextToTime(A1)之类的名称进行调用，并确保将放入其的单元格设置为时间格式，也许类似于 hh:mm:ss ./p>

I played with your question for a day and see that others have done the same. I admire the solution offered by Ron Rosenfeld using FilterXML which I briefly considered and discarded as too outlandish. It isn't. But neitehr is it neat. For "neat" look no further than the UDF below. Call it from the worksheet with something like =TextToTime(A1) and be sure to format the cell you put this in as Time, perhaps like 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的主要优势在于，仅需使用几行额外的代码即可处理几乎所有内容.Worksheeet函数没有该功能.

This function is very versatile. It can translate strings like "5 hours 10 minutes 15 seconds" or "5 hours, 10 minutes, 15 seconds" (with commas) or even "5 hours & 10 minutes and 15 seconds". It needs the spaces around the numbers but doesn't balk at extra spaces or typos. It fails on "75 minutes and 66 seconds" but that could be dealt with if it's an issue. The key advantage of VBA is that almost anything can be dealt with using just a few extra lines of code. Worksheeet functions don't have that capability.

这篇关于将Excel文本转换为时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！