2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne

This is just an idea.

1. using SUBSTITUTE() replace + - / * ^ with a common symbol.
2. using SPLIT() separate the equation into pieces
3. using IsNumber() or IsNumeric() find the pieces that are numbers

--
Gary's Student


" wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne

Interesting idea.

My idea was to LEN( Formula).
For/next loop the Formula string from Left to Right while incrementing the start point in Mid() till
Len(Formula).

That said, there are best ideas out there.

Thanks for your time/effort

EagleOne

Gary''s Student wrote:

This is just an idea.

1. using SUBSTITUTE() replace + - / * ^ with a common symbol.
2. using SPLIT() separate the equation into pieces
3. using IsNumber() or IsNumeric() find the pieces that are numbers

On Sat, 25 Nov 2006 02:37:07 GMT, wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne


Is your ROUND function supposed to be an actual Excel function? If so, the
syntax is wrong as you've left out the number of digits:

=ROUND(A1*0.035,num_digits).

Since num_digits will also be a constant, would you want that extracted also?
--ron

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

Ron Rosenfeld wrote:

On Sat, 25 Nov 2006 02:37:07 GMT, wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne


Is your ROUND function supposed to be an actual Excel function? If so, the
syntax is wrong as you've left out the number of digits:

=ROUND(A1*0.035,num_digits).

Since num_digits will also be a constant, would you want that extracted also?
--ron

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

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

--
Regards,
Luc.

"Festina Lente"


" wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne

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

Ron,
I was able to use PapaDos code as to negatives via:

Range("G" & i).Value = IIf(Left(match, 1) = "-", match, match.SubMatches(0))

That said, I am also interested =AVERAGE(1,2,3)

Both you and PapaDos spent a great deal of time for which I am very appreciative.

EagleOne


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

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

On Sun, 26 Nov 2006 13:38:01 -0800, PapaDos
wrote:

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+)"

On the OP's first example:

1 =687319 687319
2 +523187 523187
3 -7344000 7344000
4 +758450 758450
5 +2232642 2232642
6 +1995819 1995819
7 -2721538 2721538
8 +1491693 1491693
9 +723564 723564
10 *3 3

Note that the results or your routine are unsigned.

In my algorithm, I handled that problem differently, and so was able to retain
the negative signs.

1 687319
2 523187
3 -7344000
4 758450
5 2232642
6 1995819
7 -2721538
8 1491693
9 723564
10 3


--ron

On Sun, 26 Nov 2006 21:53:42 -0500, Ron Rosenfeld
wrote:

Note that the results or your routine are unsigned

That should read:

Note that the results of your routine are unsigned

--ron

LOL
That's a peculiar way to define a negative constant !
So X-Y means that Y is negative ?

Seriously, if that's what the OP wanted it could easily be fixed.
The "within string" occurences may very well be a problem too like in
MID("attention=2*too low",1,1)
--
Regards,
Luc.

"Festina Lente"


"Ron Rosenfeld" wrote:

On Sun, 26 Nov 2006 13:38:01 -0800, PapaDos
wrote:

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+)"

On the OP's first example:

1 =687319 687319
2 +523187 523187
3 -7344000 7344000
4 +758450 758450
5 +2232642 2232642
6 +1995819 1995819
7 -2721538 2721538
8 +1491693 1491693
9 +723564 723564
10 *3 3

Note that the results or your routine are unsigned.

In my algorithm, I handled that problem differently, and so was able to retain
the negative signs.

1 687319
2 523187
3 -7344000
4 758450
5 2232642
6 1995819
7 -2721538
8 1491693
9 723564
10 3


--ron

On Sun, 26 Nov 2006 13:38:01 -0800, PapaDos
wrote:

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+)"


Here's another simple function where your routine does not pick up anything:

=IF(G110000,C1,-10)

My approach picks up both:

1 10000
2 -10




--ron

PapaDos,

Code works great. I have not used VbScript 5.5 before.

Was this task just easier done in VbScript? If you have time could you share in 25 words or less
(To keep your time to a minimum) why VbScript? I realize I am exposing my ignorance.

Ron Rosenfeld also suggested VbScript 5.5

Thanks EagleOne

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

On Sun, 26 Nov 2006 16:09:33 GMT, wrote:

PapaDos,

Code works great. I have not used VbScript 5.5 before.

Was this task just easier done in VbScript? If you have time could you share in 25 words or less
(To keep your time to a minimum) why VbScript? I realize I am exposing my ignorance.

Ron Rosenfeld also suggested VbScript 5.5

Thanks EagleOne


VbScript Regular Expressions is used in both our routines so we can use Regular
Expressions, which are a much more efficient method of handling this sort of
text manipulation.


--ron

Ron,

Where is good source information on Regular Expressions? When you mentioned it previous I thought
you were referring to some subset of VBA.

EagleOne

Ron Rosenfeld wrote:

On Sun, 26 Nov 2006 16:09:33 GMT, wrote:

PapaDos,

Code works great. I have not used VbScript 5.5 before.

Was this task just easier done in VbScript? If you have time could you share in 25 words or less
(To keep your time to a minimum) why VbScript? I realize I am exposing my ignorance.

Ron Rosenfeld also suggested VbScript 5.5

Thanks EagleOne


VbScript Regular Expressions is used in both our routines so we can use Regular
Expressions, which are a much more efficient method of handling this sort of
text manipulation.


--ron

I think you should accept to match every "constants", even the "2" in
ROUND(a1,2).
Filtering out those is just about impossible...

Another problem, do we need to ignore string content, like in
=MID("this formula is 2*-3^PI",1,1)
??

And do you really want the results
2
-3
from
=A1+2-3
and not
2
3
??

--
Regards,
Luc.

"Festina Lente"


" wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne

One more thing, how about percentages (%) ?
What should be the results from
=A1*3%
=A1%
--
Regards,
Luc.

"Festina Lente"


" wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne

On Sat, 25 Nov 2006 02:37:07 GMT, wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne


Here's a somewhat cleaned up and better annotated version.

With regard to some of the issues:
it returns the negative signed values
it does NOT return "within string" constants
it returns 3% as 3
it returns all constants within a function
the output goes into the cells to the right of "selection"


============================================
Option Explicit
Sub ParseFormula()
'set up Regex
Dim objRegExp As RegExp
Dim objMatch As match
Dim colMatches As MatchCollection

' Create a regular expression object.
Set objRegExp = New RegExp
' Set Case Insensitivity.
objRegExp.IgnoreCase = True
'Set global applicability.
objRegExp.Global = True

Dim FormulaText As String
Const CellRef As String = "\$?[A-Z]{1,2}\$?\d{1,5}"
Const Operator As String = "[/*^&()=<,+]" 'No "-"
Const WithinString As String = """[^""]*"""
Const NumConstant As String = "-?(\d*\.)?\d+"
Dim Ignore As String
Dim i As Long
Dim c As Range

Ignore = Join(Array(WithinString, CellRef, Operator), "|")

'set the Regex pattern to replace unwanted stuff
objRegExp.pattern = Join(Array(WithinString, CellRef, Operator), "|")

For Each c In Selection
FormulaText = c.Formula

'Test whether the String can be compared.
If (objRegExp.Test(FormulaText) = True) Then

'change - to +- to retain negative signage
FormulaText = Replace(FormulaText, "-", "+-")

'replace unwanted stuff
objRegExp.pattern = Ignore
FormulaText = objRegExp.Replace(FormulaText, "~")

'Get the matches.
objRegExp.pattern = NumConstant
Set colMatches = objRegExp.Execute(FormulaText) ' Execute search.

End If

'Output for testing, but could go into any range
i = 1
For Each objMatch In colMatches
c.Offset(0, i).Value = objMatch
i = i + 1
Next objMatch

Next c

End Sub
===============================
--ron

Ron & PapaDos,

After using the different routines, and if I may be so bold:

What works best is that the output should be pure constants used to change cell values and not
"constants" within functions. The reason that I added (A1*3) was to make sure that I could get to
the multiplier "3" [when the OP used parenthesis to make the formula more obvious].

Actually, it would be best for the routine to ignore within-function arguements or settings.

Specifically, any numbers added, subtracted, divided, multiplied

Users have a bad habit of not identifying the pieces-parts of cell values, i.e.
Sales A Division 687319
Sales B Division 523187
Expenses A Division -7344000
Sales A Division 758450
etc, etc

My plan is to place the output of your routines into separate cells on a new worksheet so that each
constant is completely separated ready to be properly labeled and identified.

Therefore, I need to be able to

1) spin out all constants which are effectively hidden in formula strings (but not within functions
or worksheet links etc)
2) Spin any included function or link out to the same worksheet as a string

Sales A Division 687319
Sales B Division 523187
Expenses A Division -7344000
Sales A Division 758450
etc, etc xxxxxx
'='Sheet1'!A2 999000
Total xxxxxxxxxx
[This total should equal
the displayed value in
the active cell )

I have no references nor information on how to modify/create Regular Expressions

Thanks so much for you time and knowledge

EagleOne


wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells e and lsewhere on the w/s?

TIA EagleOne

On Mon, 27 Nov 2006 16:12:53 GMT, wrote:

1) spin out all constants which are effectively hidden in formula strings (but not within functions
or worksheet links etc)


So, with regard to the formula:

=ROUND(0.035,1)

what, exactly do you want?

And the references (I'm not sure why they did not come through earlier):


http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default...02&Product=vbb
http://msdn.microsoft.com/library/de...63906a7353.asp



--ron

Ron,

For ROUND(0.035,1) I do not need either of those constants nor do I need the constants in
Average[1,2,3]

Maybe another way to thing of it might me only the full string of numbers after each operator and
the equal sign like "+/-=*". I believe that will give me just the constants which should have been
in a summed worksheet list with labels.

It gets a bit more dicey when IF(X0, A1*3,A1). In this case, I want the 3 but not the 0. But the
3 should be picked up by "the full string of numbers after each operator"

What I realized later is that if the formula had four constants and a link, then a sum of all of the
constants would not add up to the original cell value in this case.

The correct cell value would be the sum of the four constants AND the value of the linked cell.

Because this gets real complicated, I thought that if all functions and the with-in constants would
be appended to the list as its own label ROUND(999.99,2).

Therefore,
= 1+2+3+4+Round(999.99,2) 'Would yield

Description
Item A 1
Item B 2
Item C 3
Item D 4
'Round(999.99,2) 1000 'if complicated to ascertain function's value, leave the value blank
Total 1010

Hopefully, I am clearer now. I'll be close by for the next 6 hours.


Ron Rosenfeld wrote:

On Mon, 27 Nov 2006 16:12:53 GMT, wrote:

1) spin out all constants which are effectively hidden in formula strings (but not within functions
or worksheet links etc)


So, with regard to the formula:

=ROUND(0.035,1)

what, exactly do you want?

And the references (I'm not sure why they did not come through earlier):


http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default...02&Product=vbb
http://msdn.microsoft.com/library/de...63906a7353.asp



--ron

On Mon, 27 Nov 2006 18:11:58 GMT, wrote:

We're getting closer to defining what you want.


Ron,

For ROUND(0.035,1) I do not need either of those constants nor do I need the constants in
Average[1,2,3]

Maybe another way to thing of it might me only the full string of numbers after each operator and
the equal sign like "+/-=*". I believe that will give me just the constants which should have been
in a summed worksheet list with labels.

It gets a bit more dicey when IF(X0, A1*3,A1). In this case, I want the 3 but not the 0. But the
3 should be picked up by "the full string of numbers after each operator"

What I realized later is that if the formula had four constants and a link, then a sum of all of the
constants would not add up to the original cell value in this case.

The correct cell value would be the sum of the four constants AND the value of the linked cell.

Because this gets real complicated, I thought that if all functions and the with-in constants would
be appended to the list as its own label ROUND(999.99,2).

Therefore,
= 1+2+3+4+Round(999.99,2) 'Would yield

Description
Item A 1
Item B 2
Item C 3
Item D 4
'Round(999.99,2) 1000 'if complicated to ascertain function's value, leave the value blank
Total 1010

Hopefully, I am clearer now. I'll be close by for the next 6 hours.


Aside from the fact that ROUND(999.99,2) -- 999.99 and not 1000, there remains
a problem in differentiating functions from which you want to extract constants
from those you don't wish to execute an extraction.

IF(X0,A1*3,A1)

you write extract the 3 using the "number string after operator algorithm"/

BUT that fails with

IF(X0,3*A1,A1)

or even

IF(X0,3,1)

==========================================

It seems to me it would be a whole lot simpler to develop a user form that
would ensure that entries are made properly.




--ron