| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Terms and Exponents of a Text Poly Formula ??
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting Terms and Exponents of a Text Poly Formula ??
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 Longres 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 "monir" wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting Terms and Exponents of a Text Poly Formula ??
Jerry;
Thank you kindly for your prompt and thoughtful reply. Will review it shortly and get back to you. Regards. "Jerry W. Lewis" wrote: 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 Longres 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 "monir" wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting Terms and Exponents of a Text Poly Formula ??
Hi Jerry:
Sorry for the delay in getting back to you. I had some difficulty in properly aligning the provided VBA source code!! Perfect! Your procedure works fine and as intended. I put your functions (WordCount & Word) in a standard module, entered the poly equation in cell B1, and entered the formula for the no. of terms in C5:: =WordCount(Word(B1,1,"="),"+-") The complexity of your code clearly demonstrates that the task at hand is by no means a trivial or a simple one! Parsing a general multivariant non-linear polynomial equation, extracting the coefficient and the exponent of each variable in each term, and tabulate the values in a column ?? The task doesn't appear to be feasible to me at this time! Not ready yet to give up! Thanks again for your time and help. "monir" wrote: Jerry; Thank you kindly for your prompt and thoughtful reply. Will review it shortly and get back to you. Regards. "Jerry W. Lewis" wrote: 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 Longres 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 "monir" wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting Terms and Exponents of a Text Poly Formula ??
Hi Jerry;
FYI and for those who might be interested in the subject, I've just posted at: http://www.mrexcel.com/forum/showthr...96#post2073296 a summary (in a point format) of three (3) successful procedures: (1) Work Sheet Solution: (no code required) (2) VBA Code Procedu Sub PolyNEq(), using Dictionary & RegExp objects (3) VBA Code Procedu Sub ParseEquation_3() Please let me know if you have any comments. Regards. "monir" wrote: Hi Jerry: Sorry for the delay in getting back to you. I had some difficulty in properly aligning the provided VBA source code!! Perfect! Your procedure works fine and as intended. I put your functions (WordCount & Word) in a standard module, entered the poly equation in cell B1, and entered the formula for the no. of terms in C5:: =WordCount(Word(B1,1,"="),"+-") The complexity of your code clearly demonstrates that the task at hand is by no means a trivial or a simple one! Parsing a general multivariant non-linear polynomial equation, extracting the coefficient and the exponent of each variable in each term, and tabulate the values in a column ?? The task doesn't appear to be feasible to me at this time! Not ready yet to give up! Thanks again for your time and help. "monir" wrote: Jerry; Thank you kindly for your prompt and thoughtful reply. Will review it shortly and get back to you. Regards. "Jerry W. Lewis" wrote: 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 Longres 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 "monir" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for extracting text formated numbers within ( )'s as numbe | Excel Discussion (Misc queries) | |||
| conditional formula in terms of cell formats | Excel Worksheet Functions | |||
| I cannot display all the poly equation constants on the chart. | Charts and Charting in Excel | |||
| exponents in excel | Excel Worksheet Functions | |||
| sum of a series of exponents | Excel Worksheet Functions | |||
Linear Mode
