On Sat, 25 Nov 2006 21:45:02 -0800, PapaDos
wrote:

Something like this ?

Sub extractConstants()
Dim range_to_check As Range
Dim re As New RegExp
Dim matches, match, i, c

Set range_to_check = [A1:A4] ' SET AS NEEDED

re.Global = True
re.Pattern = "[=(^*/+\-](-*\d*\.?\d+)"
For Each c In range_to_check
Set matches = re.Execute(c.Formula)
For Each match In matches
i = i + 1
Debug.Print i, match, match.SubMatches(0)
Next
Next
End Sub

Nice regex. I was trying to think of something like that but could not.

But it fails on formulas such as:

=AVERAGE(1,2,3)

It also fails to return the negative values, as the OP mentioned in a
subsequent post.


--ron