Excel VBA如何批量将字符串内epoch时间戳替换为可读日期

VBA调用的VBScript.RegExp对象原生Replace方法不支持直接传入自定义函数作为替换回调，因此我们可以先通过Execute方法提取单元格内所有符合规则的时间戳，再倒序遍历匹配项逐个替换，避免正序替换导致的字符串偏移问题。

完整可运行代码 #

' 原有时间转换函数无需修改
Function ux2pst(uts)
ux2pst = Format(DateAdd("s", uts, "12/31/1969 16:00:00"), "MM/DD/YYYY HH:MM")
End Function

Sub replaceEpoch()
    Dim regEx As Object
    Dim r As Range, rT As Range
    Dim matches As Object, match As Object
    Dim strTemp As String
    Dim i As Long
    
    Set r = Range("B2", Cells(Rows.Count, "B").End(xlUp))
    
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Pattern = "\d{10}"
    regEx.Global = True
    
    For Each rT In r
        strTemp = rT.Value
        If strTemp <> "" Then
            ' 提取当前单元格所有匹配的时间戳
            Set matches = regEx.Execute(strTemp)
            If matches.Count > 0 Then
                ' 倒序遍历匹配项，避免替换后字符串长度变化导致位置偏移
                For i = matches.Count - 1 To 0 Step -1
                    Set match = matches(i)
                    ' 替换对应位置的时间戳为转换后的时间
                    strTemp = Left(strTemp, match.FirstIndex) & ux2pst(CLng(match.Value)) & Mid(strTemp, match.FirstIndex + match.Length + 1)
                Next i
                rT.Value = strTemp
            End If
        End If
    Next rT
    ' 释放对象
    Set regEx = Nothing
    Set matches = Nothing
    Set match = Nothing
End Sub

注意事项 #

• 如果表格其他位置也可能出现非时间戳的10位数字，可以把正则规则调整得更精准，将regEx.Pattern改为(?<=\<\[)\d{10}(?=\])，仅匹配<[和]包裹的10位数字，避免误替换。
• 运行宏前建议先备份原表格数据，避免替换出错无法回滚。

内容的提问来源于stack exchange，提问作者lilyops