On Sat, 27 Jan 2007 08:46:04 GMT, Ken McLennan
wrote:

I have no clue when it comes to reg expressions, and don't even
know whether any such thing would be a useful technique in this
instance.

Here's a routine, using regular expressions, that will extract the Time and the
a or the p.

It works on your examples.

However, if there were a number preceding the time that was not a time, it
would have to be modified.

It will return the existing time string and, if present in the original, an "a"
or a "p".

If some of the entries in your data differ significantly from what you've
posted, small changes in the "regex" may be required.

------------------------------------------
Option Explicit

Sub TestTimeParser()
Dim T(4) As String
Dim i As Long
T(0) = "6"
T(1) = "6A"
T(2) = "11:41 on call"
T(3) = "6:00 am On Call"
T(4) = "6:00pm On Call"

Dim objRe As Object
Dim colMatches As Object
Const Pattern As String = "[0-9:]+(\s?[ap])?"

Set objRe = CreateObject("vbscript.regexp")
objRe.Global = True
objRe.Pattern = Pattern
objRe.ignorecase = True

For i = 0 To UBound(T)

If objRe.test(T(i)) = True Then
Set colMatches = objRe.Execute(T(i))
Debug.Print Replace(colMatches(0), " ", "")
End If

Next i

End Sub
----------------------------------------


--ron