On Sun, 2 Mar 2008 16:24:47 -0800 (PST), Bernd P wrote:

Hello,

I suggest to take the UDF regexpreplace:

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

(you can call it via VBA with another first parameter - the last
parameter defines the n-th return value. I you use "$4" it would
return 10.00 for your second sample row).

The function you can find he http://www.sulprobil.com/html/regexp.html

Regards,
Bernd

The OP wrote: "The date code, "05" and time code "PM" never change in text
LENGTH."

I think it unlikely that they never change at all. If they do, your regex will
fail.

It will also fail to extract the proper value if any of the values should ever
be negative numbers.

I think Rick's solution, or some variation on it (using the VBA Split
function), is probably the most efficient in VBA.

If I were going to use a regex variation, I would do something like, using the
code below:

A1: Data
B1: =ReExtr($A2,"[-+]?\b\d*\.?\d+\b",-8+COLUMNS($A:A))
(The "8" is one more than the number of numeric entries at the end to be
parsed. This last argument is the Index which, if negative, counts backward
from the last match)

Fill right to H1
Select B1:H1 and fill down as far as required

================================================== ====
Option Explicit
Function ReExtr(Str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True, _
Optional MultiLin As Boolean = False)

Dim objRegExp As Object, objMatch As Object, colMatches As Object
Set objRegExp = CreateObject("vbscript.regexp")
With objRegExp
.Pattern = Pattern
.IgnoreCase = Not CaseSensitive
.Global = True
.MultiLine = MultiLin
End With

If (objRegExp.Test(Str) = True) Then
Set colMatches = objRegExp.Execute(Str)
ReExtr = CStr(colMatches(IIf(Index 0, Index - 1, _
colMatches.Count + Index)))
End If
End Function
==============================

--ron