On Sun, 2 Mar 2008 23:46:08 -0800 (PST), Bernd P wrote:

Hello Ron,

IMHO it is a nice and robust approach to take the UDF regexpreplace as
a "black box" and then to call it (may it be from a worksheet or from
within VBA) with:

=regexpreplace(A1,"^05 (.+) PM" & REPT(" ([-+]?\d*\.?\d*)",7) &
"$","$1")

I have no problem to accept a better fitting regular expression here
but I would not even seek for a most efficient solution in VBA. If
efficiency is an issue (in terms of runtime) I would do the
preprocessing with sed or perl...

Regards,
Bernd

Be that as it may, the OP was looking for a VBA solution. Rick's previously
posted solution is quite effective. Both of our regex solutions take about
fifty (50) times as long to extract the first number after the PM.

Longre's Regex.Mid function, part of the morefunc.xll add-in, runs about twice
as fast as either of ours.

I see you've adapted yours to include both positive and negative numbers, but
it still will work only with date code of 05 and time code of PM.

I agree the Regex solutions are simpler to implement for complicated
extractions.
--ron