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

G'day there Ron,

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

It works on your examples.

Then it will more than likely work on the real data, too.

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

I can do a bit of 'pre-cleaning' to, hopefully, alleviate such a
thing.

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

That's what I'm after. If there's no "a" or "p" it's not a problem
as long as the string on the other side of the hyphen has one. I've
already written code that calculates the correct letter from what's on
the other side. It's a bit clumsy, but it works so I'm not gonna touch
it <g.

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

Nodnodnod. I'll give it a try tonight. The first half (before the
hyphen) is not a problem as there are no code letters introduced
adjacent to the hyphen (thank the Gods for small mercies). In one small
aspect over which I have some control, I've requested (don't have
authority to TELL anyone) that a space be left after the shift ending
time to differentiate between times and any extra characters. I can't
enforce it but I did ask politely =).

Set objRe = CreateObject("vbscript.regexp")

Is that a standard library? I'll have to check after my days off
to see if it's installed. If not then I'm out of luck (dammit!!) Still,
I'll just have to try it and see.

Thanks very much for your help. It's greatly appreciated.

--
See ya,
Ken McLennan
Qld, Australia

On Sun, 28 Jan 2007 07:37:29 GMT, Ken McLennan
wrote:

Set objRe = CreateObject("vbscript.regexp")

Is that a standard library? I'll have to check after my days off
to see if it's installed. If not then I'm out of luck (dammit!!) Still,
I'll just have to try it and see.

Yes, it is.

You could also set a reference (Tools/References) to Microsoft VBScript Regular
Expressions 5.5 and use this equivalent routine, which should run faster.

=============================
Option Explicit

Sub ParseNames()
Const T As String = "Name1 Name2 Name3 Name4"
Dim i As Long

Dim objRe As RegExp
Dim colMatches As MatchCollection

Const Pattern As String = "\w+"

Set objRe = New RegExp
objRe.Pattern = Pattern
objRe.Global = True

If objRe.test(T) = True Then
Set colMatches = objRe.Execute(T)

For i = 0 To colMatches.Count - 1
Debug.Print colMatches(i)
Next i

End If
End Sub

==========================
--ron

On Sun, 28 Jan 2007 07:37:29 GMT, Ken McLennan
wrote:

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

I can do a bit of 'pre-cleaning' to, hopefully, alleviate such a
thing.

If you can present the variability in the original data, it may be easy to
modify the regex "Pattern"


--ron