On Sat, 25 Nov 2006 14:20:41 GMT, wrote:

An excellent question. The answer is No to formula arguments. I need just the constants used by or
in the formula.

That makes it very difficult.

Without that requirement, you could easily strip out the operators and cell
references by using Regular Expressions, and then return everything else that
is numeric.

With the requirement that numeric arguments to functions not be returned, such
as the type I mentioned, you would have to have some kind of table to analyze
each function. I can't think of any other way to, for example, given:

=ROUND(.035,1)

to extract the ".035" but not extract the "1"


--ron

Appreciate your time & knowledge. I agree, the challenge is awesome.

EagleOne

Ron Rosenfeld wrote:

On Sat, 25 Nov 2006 14:20:41 GMT, wrote:

An excellent question. The answer is No to formula arguments. I need just the constants used by or
in the formula.

That makes it very difficult.

Without that requirement, you could easily strip out the operators and cell
references by using Regular Expressions, and then return everything else that
is numeric.

With the requirement that numeric arguments to functions not be returned, such
as the type I mentioned, you would have to have some kind of table to analyze
each function. I can't think of any other way to, for example, given:

=ROUND(.035,1)

to extract the ".035" but not extract the "1"


--ron

On Sat, 25 Nov 2006 16:11:13 GMT, wrote:

Appreciate your time & knowledge. I agree, the challenge is awesome.

EagleOne

If I were going to do that, I think I would first replace every character that
is a function parameter with a nonsense string, perhaps a tilde ~.

Then replace all the cell references and operators with tilde's.

Then extract the numbers that remain.

I would use regular expressions to do all that.
--ron

I am in the process of doing just that, I'll post back when I get it.

Ron Rosenfeld wrote:

On Sat, 25 Nov 2006 16:11:13 GMT, wrote:

Appreciate your time & knowledge. I agree, the challenge is awesome.

EagleOne

If I were going to do that, I think I would first replace every character that
is a function parameter with a nonsense string, perhaps a tilde ~.

Then replace all the cell references and operators with tilde's.

Then extract the numbers that remain.

I would use regular expressions to do all that.
--ron

Ron,

Here is what I came up with. Any thoughts for improvement appreciated.

For testing, the "formula" I used in "A1":
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(C1*3)

My results a
687319~523187~7344000~758450~2232642~1995819~27215 38~1491693~723564~3

Because I changed the operational signs to "~" I have lost positive vs negative numbers.

Can you think of a way to preserve the positive vs negative numbers?

EagleOne

Ron Rosenfeld wrote:

On Sat, 25 Nov 2006 16:11:13 GMT, wrote:

Appreciate your time & knowledge. I agree, the challenge is awesome.

EagleOne

If I were going to do that, I think I would first replace every character that
is a function parameter with a nonsense string, perhaps a tilde ~.

Then replace all the cell references and operators with tilde's.

Then extract the numbers that remain.

I would use regular expressions to do all that.
--ron