Extracting substrings from strings using regex.

[MrTeeny]

Can anyone give me the VBA to extract a time from a string, it'd always appear in the format ##:## but not always in the same place otherwise I'd have just used split to extract it. I did consider using split with a colon separator and then just joining 2 digits from left and right of the two items in the array but thought there must be a neater way using VBA.

Thanks

 

[admiral100]

Hi,

try this :

Private Function RegExTime(s As String) As String
Dim myval, match
Set myval = CreateObject("vbscript.regexp")
myval.Pattern = "(?:[01]?\d|2[0-3]):[0-5]\d"
myval.Global = True


For Each match In myval.Execute(s)

RegExTime = match.Value
Exit For
Next
Set myval = Nothing

End Function

 

[MrTeeny]

Thanks, works perfectly.