學不完．教不停．用不盡

有網友問到：如果有一個時間數列其分和秒是以「'、"」表示，這個數列的時間是以文字格式儲存，無法拿來進一步運算，如何將其轉換為真正的時間呢？

這個問題很實用，也看過有人使用這樣的公式：

儲存格B2：

=MID(A2,1,FIND("'",A2)-1)&"分"&MID(A2,FIND("'",A2)+1,FIND("""",A2)-FIND("'",A2)-1)&"秒"

找到「'」和「"」的位置，再以 MID 函數取出分和秒的數值。

不過我建議使用 SUBSTITUTE 函數會比較簡單：

儲存格C2：=SUBSTITUTE(SUBSTITUTE(A2,"'",":"),CHAR(34),"")

將「'」換成「分」，將「"」換成「秒」。

其中因為 SUBSTITUTE 函數無法直接置換「"」符號，所以將「"」符號以 CHAR(34) 替代(「"」的 ASCII 數值為 34)。

但是以上的轉換結果為文字，仍不是數值(時間)，無法拿來正確的運算，所以要再轉換一次。

儲存格D2：=TIMEVALUE("0:"&SUBSTITUTE(SUBSTITUTE(A2,"'",":"),CHAR(34),""))

利用 TIMEVALUE 函數將文字轉換為時間，注意到公式中有將原來的時間之前串接「"0:"」字串，原因是 Excel 會將 7:6 視為 7 時 6 分，所以串接「"0:"」之後可以變為 0 時 7 分 6 秒。

再利用數值格式設定將其轉換為以「:」表示的時間，如此的時間就可以拿來運算了！