Like you said before, it is probably not possible to adjust it for every
circumstances (Average(1,2,3) and so on...).

But it should pick up the negative values !
Any example where it failed to do so ?

I didn't allowed spaces in my first version, this Regexp shoud fix that:
re.Pattern = "[=(^*/+\-]\s*(-*\s*\d*\.?\d+)"


--
Regards,
Luc.

"Festina Lente"


"Ron Rosenfeld" wrote:

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