vba - How to remove text before a certain character?

Question

Let's say I have the following values:

1 min 7 sec
23 sec
6 hours 10 min 14 sec

I want to return:

7 sec
23 sec
14 sec

Is there a way to do this in VBA?

Sec, Min, or Hours may not always be present if a value for it is not used.

I.e. if there are 60 seconds, it will just say 1 min. If there are 60 minutes exactly, it will just say 1 hour.

标签： vbaexcel

Answer 1

这是一种使用正则表达式的方法。它查找数字空间秒，如果找不到则返回连字符。修改以适应。

修改代码以纳入@Mathieu Guindon 的建议。+寻找一个或多个实例，*寻找零个或多个。

Function Regex1(v As Variant) As String

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "\d+\s*sec"
    If .Test(v) Then
        Regex1 = .Execute(v)(0)
    Else
        Regex1 = "-"
    End If
End With

End Function