Excel natively has very minimal capabilities for parsing expressions, but you
can write functions in VBA to extend the capabilities. Using the VBA
functions below, you would put in C5 the formula
=WordCount(Word(B1,1,"="),"+-")
You would likely need to write a few additional functions to complete
everything, but this should give you a leg up.
Alternately, you could download Laurent Longre’s morefunc.xll Add-In from
http://xcell05.free.fr/morefunc/english/index.htm
where the comparable C5 formula would be
=WORDCOUNT(WMID(B1,,1,"="),"+-")
Jerry
' Count the number of words in a text string
'
' Input parameters:
' text text string for which the number of words is to be counted
' delimiters text string all characters that may terminate or separate
words
' but are not part of a word (default=" ")
' If delimiters is specified and does not include a space,
' text must either not contain chr(1) or else not contain
spaces
' SingleDelimiter whether to consider adjacent delimiters as enclosing a
' zero length word (TRUE), or not (FALSE) (default=FALSE)
'
' Method: Remove delimiter characters from the string and calculate the
difference in length
'
' Notes: A "word" is one or more characters or digits (other than delimiter
characters)
' that is terminated by a delimiter or the end of the string
' unless singleDelimiter = True, in which case every delimiter
marks the
' end of one "word" (possibly zero length) and the beginning of
another
' A number will be counted as one word
'
Function WordCount(ByVal text, Optional ByVal delimiters = " ", Optional
singleDelimiter = False)
If IsMissing(text) Then WordCount = [#VALUE!]: Exit Function
If Len(text) = 0 Then WordCount = 0: Exit Function
Dim d1 As String, d2 As String, j As Long, cf As Long
d1 = Left(delimiters, 1&) ' primary delimiter
If d1 = "" Then d1 = " " ' default delimiter is space
For j = 2& To Len(delimiters) ' replace all delimiters
with primary delimiter
text = Replace(text, Mid(delimiters, j, 1&), d1)
Next j
cf = 1& ' correction factor to
convert length difference to word count
If Not singleDelimiter Then
cf = cf - Abs(Left(text, 1&) = d1) - Abs(Right(text, 1&) = d1)
d2 = IIf(d1 = "_", "-", "_") ' any character different
than d1 (possibly in text, possibly a delimiter)
text = Replace(text, d1 & d1, d2 & d1) ' ignore all but last of
consecutive delimiters
text = Replace(text, d1 & d1, d2 & d1) ' 2nd pass needed in case
odd (>1) number of consecutive delimiters
text = Replace(text, d1 & d2, d2 & d2) ' only last of consecutive
delimiters is left
End If
WordCount = Len(text) - Len(Replace(text, d1, "")) + cf
End Function
' Return the ith word in a text string
'
' Input parameters:
' text text string from which the ith words is to be extracted
' i number of the word to be returned
' nwords number of words to be returned (default=1)
' trimIt whether to return a simple list of the nwords(>1) words
' separated by the first delimiter (TRUE),
' or the exact substring text containing the
' nwords(>1) words (FALSE). (default=False)
' delimiters text string all characters that may terminate or separate
words
' but are not part of a word (default=" ")
' If delimiters is specified and does not include a space,
' text must either not contain chr(1) or else not contain
spaces
' SingleDelimiter whether to consider adjacent delimiters as enclosing a
' zero length word (TRUE), or not (FALSE) (default=FALSE)
'
' Method: Change all but last delimiter before ith and (i+nwords)th words to
someting else,
' then get location with InStr()
'
' Notes: A "word" is one or more characters or digits (other than delimiter
characters)
' that is terminated by a delimiter or the end of the string
' unless singleDelimiter = True, in which case every delimiter
marks the
' end of one "word" (possibly zero length) and the beginning of
another
' A number will be counted as one word
' If i is not an integer, it is truncated toward zero
' If i=0 or Abs(i)>WordCount(string), a null string is returned
'
Function Word(ByVal text, ByVal i, Optional ByVal nwords = 1, Optional ByVal
trimIt = False, Optional ByVal delimiters = " ", Optional singleDelimiter =
False)
If VarType(nwords) = vbString Then delimiters = nwords: singleDelimiter
= trimIt: nwords = 1: trimIt = False
If IsMissing(text) Then Word = [#VALUE!]: Exit Function
If IsMissing(i) Or Not IsNumeric(Val(i)) Or Not IsNumeric(nwords) Then
Word = [#NUM!]: Exit Function
i = Fix(i): nwords = Fix(nwords)
If i = 0& Or Len(text) = 0 Or nwords = 0 Then Word = "": Exit Function
Dim d1 As String, d2 As String, j As Long, cf As Long, n As Long, k As
Long, text0 As String, text1 As String
If Not trimIt Then text0 = text ' retain original delimiters
for return value
d1 = Left(delimiters, 1&) ' primary delimiter
If d1 = "" Then d1 = " " ' default delimiter is space
For j = 2& To Len(delimiters) ' replace all delimiters
with primary delimiter
text = Replace(text, Mid(delimiters, j, 1&), d1)
Next j
text1 = text ' retain uniform delimiters
for locating end of last word and easy trimming
d2 = IIf(d1 = "_", "-", "_") ' any character different
than d1 (possibly in text, possibly a delimiter)
cf = 1&: j = 1& ' correction factor to
convert length difference to word count
If Not singleDelimiter Then
j = j - Abs(Left(text, 1&) = d1)
cf = cf - Abs(Left(text, 1&) = d1) - Abs(Right(text, 1&) = d1)
text = Replace(text, d1 & d1, d2 & d1) ' ignore all but last of
consecutive delimiters
text = Replace(text, d1 & d1, d2 & d1) ' 2nd pass needed in case
odd (>1) number of consecutive delimiters
text = Replace(text, d1 & d2, d2 & d2) ' only last of consecutive
delimiters is left
End If
n = Len(text) - Len(Replace(text, d1, "")) + cf ' WordCount
' find last word of return string
If i < 0& Then i = n + 1& - Abs(i)
If i <= 0& Or i > n Then Word = "": Exit Function
If nwords < 0 Then k = i: i = i + nwords + 1: nwords = -nwords Else k =
i + nwords - 1 ' k is # of last word of return string
If i <= 0& Then nwords = nwords + i - 1: i = 1
If k > n Then nwords = nwords + (n - k): k = n
k = InStr(Replace(text, d1, d2, , k - 1 - j), d1) + 1 ' k is start of
last word
' find length of last word
Word = Mid(text1, k)
If InStr(Word, d1) = 0 Then k = Len(text) + 1 Else k = k + InStr(Word,
d1) - 1 ' k is 1st position after last word
i = InStr(Replace(text, d1, d2, , i - 1 - j), d1) + 1
' i is start of first word
If trimIt Then
Word = Mid(text1, i, k - i) ' substring with all
delimiters converted to d1
Do ' trim successive internal
delimiters
n = Len(Word)
Word = Replace(Word, d1 & d1, d1)
Loop Until Len(Word) = n
Else
Word = Mid(text0, i, k - i) ' substring with original
delimiters
End If
End Function
:
Hello;
(Sorry for cross-posting, but no expert has taken it on from the other XL
forum, suggesting the answer is either too simple or too difficult or simply
can't be done using w/s functions/macro!)
1) This exercise would save me considerable time and would almost certainly
eliminate the possibility of typing errors in preparing the input data file
for another application (program).
2) My program requires each input multivariant polynomial equation to be
specified as follows and in the same order:
--Number of Independent Variables, N (max 10)
--Number or Terms, M (max 20)
--Term 1: power of var 1
: power of var 2
: ...................
: power of var N
: coeff
--Term 2: power of var 1
: power of var 2
: ...................
: power of var N
: coeff
....................................
--Term M: power of var 1
: power of var 2
: ...................
: power of var N
: coeff
3) It would be extremely helpful if I could type (or cut/paste) the equation
(of up to 10 variables and up to 20 terms) in a cell on a w/s and
automatically get the above particulars extracted and nicely tabulated in a
column.
4) Simplified Example: (3 var & 4 terms)
cell B1 enter :: 1.5*X + 2.*X*Y^2 - 3.0*Z*Y + 4.5*X*Z^4 = 0.0
cell B2 enter no. of variables :: 3
cell B3:B3+N-1 enter var names :: X Y Z
Expected Results:
Number of Terms M, cell C5 :: 4
Term 1: power of var 1, cell C6 :: 1
: power of var 2, cell C7 :: 0
: power of var 3, cell C8 :: 0
: coeff , cell C9 :: 1.50
Term 2: power of var 1, cell C10 :: 1
: power of var 2, cell C11 :: 2
: power of var 3, cell C12 :: 0
: coeff , cell C13 :: 2.00
Term 3: power of var 1, cell C14 :: 0
: power of var 2, cell C15 :: 1
: power of var 3, cell C16 :: 1
: coeff , cell C17 :: -3.00
Term 4: power of var 1, cell C18 :: 1
: power of var 2, cell C19 :: 0
: power of var 3, cell C20 :: 4
: coeff , cell C21 :: 4.50
Could someone please provide some guidance on how to accomplish that ??
Thank you kindly.
