網(wǎng)友求助SOS：如何批處理1000條混亂的時間數(shù)據(jù)？到底是什么工作場景呢，下面我們舉例還原真實的職場案例。

如下圖所示：

A列是一列相對格式混亂的文本型時間數(shù)據(jù)，有的是"x時x分x秒"格式，有的是"x秒"格式，也有的是"x分x秒"格式，我們想要將這些格式不太一樣的時間數(shù)據(jù)轉(zhuǎn)換為統(tǒng)一的數(shù)字型時間格式"h:mm:ss"。比如"49時25分7秒"被轉(zhuǎn)成了"49:25:07"，而像"7秒"變成了"0:00:07"。

微軟Office LTSC 2021專業(yè)增強版 簡體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語言：簡體中文
• 時間：2024-09-12

查看詳情

WPS Office 2025 V12.1.0.25865 官方最新正式版 32位/64位

• 類型：辦公軟件
• 大?。?/span>280MB
• 語言：簡體中文
• 時間：2026-04-16

查看詳情

有的小伙伴還可能注意到：有“小時”或“時”的不同情況，比如“1小時”或“49時”。

這種問題看似很難，無法立即找到解題的方法。但是相信只要我們掌握了Excel常用的基礎(chǔ)的函數(shù)，并找到科學(xué)的嚴謹?shù)乃悸?，就可以嘗試解決。

第一步：提取數(shù)字

輸入公式：

=REGEXP(A2,"\d+(?="&{"小|時","分","秒"}&")")

通過REGEXP正則表達式函數(shù)提取“時/小時、分、秒”前的數(shù)字。

匹配規(guī)則解釋：

\d+：匹配一個或多個數(shù)字。

正則表達式里的(?=...)是正向肯定預(yù)查，匹配數(shù)字，符合數(shù)字后面跟著的是小時/時、分或秒的情況。

• (?=小|時)：匹配“時”或“小時”前的數(shù)字（如49時→49）。符號“|”是邏輯“或”的意思。
• (?=分)：匹配“分”前的數(shù)字（如25分→25）。
• (?=秒)：匹配“秒”前的數(shù)字（如7秒→7）。

最后返回數(shù)組：{小時, 分, 秒}。比如49時25分7秒→{49,25,7}，而匹配不到數(shù)字后面跟著的是小時/時、分或秒的情況時，會返回錯誤值。比如：7秒→{#N/A,#N/A,7}。

第二步：缺失單位處理

外嵌IFNA公式：

=IFNA(REGEXP(A2,"\d+(?="&{"小|時","分","秒"}&")"),)

IFNA函數(shù)有一個常見用法，當(dāng)?shù)谝粎?shù)為錯誤值，且跳過第2參數(shù)時，會返回固定的“0”值。

所以，此步驟的目的是處理未匹配到的時間單位（如文本中無“時”與“分”時，默認補0）。

若某單位未匹配到，對應(yīng)位置留“0”。比如：7秒→{"0", "0", "7"}

第三步：連接字符串

外嵌EXTJOIN函數(shù)：

=TEXTJOIN(":",,IFNA(REGEXP(A2,"\d+(?="&{"小|時","分","秒"}&")"),))

TEXTJOIN(":", , ...) 結(jié)構(gòu)功能：

用冒號“:”連接小時、分、秒。生成時間格式文本字符串。

比如：{49,25,7}→49:25:7

再比如：{"0","0","7"}→0:0:7

第四步：文本格式數(shù)值化

輸入公式：

=TEXTJOIN(":",,IFNA(REGEXP(A2,"\d+(?="&{"小|時","分","秒"}&")"),))*1

...*1 結(jié)構(gòu)功能：

將文本時間轉(zhuǎn)換為Excel可識別的數(shù)字序列號。

Excel將時間視為小數(shù)（1小時=1/24，1分鐘=1/1440）。

例如 49:25:7 會轉(zhuǎn)換為表示總時長的數(shù)值"2.0591087962963"。

第五步：格式化為時間

外嵌TEXT函數(shù)：

=TEXT(TEXTJOIN(":",,IFNA(REGEXP(A2,"\d+(?="&{"小|時","分","秒"}&")"),))*1,"[H]:mm:ss")

TEXT(..., "[H]:mm:ss") 結(jié)構(gòu)功能：

將序列號格式化為標(biāo)準(zhǔn)時間格式。

[H]顯示超過24小時的總時長（如49小時直接顯示為49）。

mm和ss補足兩位，如7秒→07。

推薦閱讀：wps怎么排列時間順序? WPS里利用AI編寫代碼對行內(nèi)時間正向排序技巧

最新評論