excel文字到时间 [英] excel text to time

问题描述

我有一个excel表，其格式与文本类似下面的例子。我需要将文本字符串的时间转换成可用的格式。我会很高兴，如果我可以得到它所有的相同格式I.E.一切在几秒钟内从那里工作。我知道我可以使用一个帮助列和一个非常长的丑陋的IF左边，右边，中间的公式和除了所有的东西，但我正在与数千这些日常工作，并希望自动化它更好的一个动态公式或VBscript使这些更容易使用。

I have an excel table with the time formatted with text like the example below. I need to convert the time from a text string into a usable format. I would be happy if I could get it all in the same format I.E. everything in seconds and work from there. I know I could do this using a helper column and a very long ugly IF left, right, mid formula and divide everything but I am working with thousands of these daily and would love to automate it better with a dynamic formula or VBscript that made these easier to work with.

     A         B
1 Server(A)  15d 3h 39m
2 Server(E)  3h 36m 44s
3 Server(C)  4m 3s
4 Server(B)  44s

推荐答案

这不是优雅的，只要你的格式如下所述，它就可以用作UDF：

This isn't elegant, but it would work as a UDF as long as your format is as described:

Public Function timeStringToSeconds(strIn As String) As Long
    Dim values
    values = Split(strIn, " ")
    For Each v In values
        Select Case Right$(v, 1)
            Case "d"
                timeStringToSeconds = timeStringToSeconds + CLng(Left$(v, Len(v) - 1)) * 86400
            Case "h"
                timeStringToSeconds = timeStringToSeconds + CLng(Left$(v, Len(v) - 1)) * 3600
            Case "m"
                timeStringToSeconds = timeStringToSeconds + CLng(Left$(v, Len(v) - 1)) * 60
            Case "s"
                timeStringToSeconds = timeStringToSeconds + CLng(Left$(v, Len(v) - 1))
        End Select
    Next
End Function

您可以简单地使用它：在C1中，例如： timeStringToSeconds（B1）
或者通过执行以下操作在范围上运行它：
range.value = timeStringToSeconds（range.value）

You could use it simply by doing this: in C1 for example: timeStringToSeconds(B1) Or run it on a range by doing something like this: range.value = timeStringToSeconds(range.value)

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