☰

如何將時間字串轉換為秒

tag-icon • tag-icon microsoft-excel
如何將時間字串轉換為秒

我有大約 5000 行的 CSV 檔案匯入到 Excel 中，我的問題是我有多個類似的時間字串1h1m1s，需要將它們轉換為秒。

以下是幾個例子：

A 列中的數據

0m11s         
2m32s        
3m10s        
1h2m35s

B 欄結果

11 seconds
152 seconds  
190 seconds    
3755 seconds

我嘗試過設定單元格格式，但最終得到了一些奇怪的結果！

是否可以這樣做，如果可以，那麼我如何將時間字串轉換為秒？

答案1

使用 VBA 這相對容易。

一種方法是使用正規表示式來解析字串，然後將每個部分乘以適當的轉換。

若要輸入此使用者定義函數 (UDF)，alt-F11請開啟 Visual Basic 編輯器。確保您的專案在「專案資源管理器」視窗中突出顯示。然後，從頂部選單中選擇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)

為了方便閱讀，我在時間分量之間的公式中添加了換行符，因此請刪除它們以複製並貼上公式。

時間很簡單。它採用左側“h”之前的字符，如果缺少“h”則採用零，並將其乘以 3600。

分鐘值前面只能有小時。如果存在分鐘值，則會根據是否存在小時部分來計算 MID 的起點和長度。

秒值的位置（如果存在）取決於小時和分鐘部分中的一個或兩個部分的存在。 「m」（如果存在）的位置在字串中始終晚於「h」（如果存在）的位置。如果其中一個不存在，則公式對該分量使用零值。然後，MAX 函數提供原始字串中秒值之前的最遠位置，並確定先前的時間分量佔用了字串長度的多少。

輔助列可以隱藏。它們可以合併到 E 列的公式中，但如果您查看這些值的使用次數，您會發現產生的公式將非常長且難以維護。

相關內容