我玩了一天您的问题，发现其他人也做了同样的事情.我很佩服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.