adding numbers with letters

On Wed, 9 Jan 2008 07:16:59 -0800 (PST), wrote:

Thanx, you guys are great! Worked fine after tweaking security
settings.

One last question, sometimes the addends are fractions such as:

88 1/2grn+3wht+88 1/2grn

How do you tweak the code to see these?

Here's a bit shorter routine that should do that:

==============================================
Option Explicit
Function SumNums(str As String) As Double
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[A-Za-z]"
SumNums = Evaluate(re.Replace(str, ""))
End Function
==============================================
--ron

Here is a modification to the code I just posted which should survive most (if not all) combinations of characters following a valid number in each of the addends making up your text. That is, a text string like this should be evaluated fine....

88 1/2../ grn-5.5 /. / wht+88 1/2//.//grn

I'm not sure if your text could ever be this malformed, but the code will function correctly it if it can be. Use the same instructions for implementing the code below my signature as I gave you in my previous posting.

Rick

Function SumCharacters(Rng As Range) As Double
Dim X As Long
Dim Y As Long
Dim Addends() As String
If Rng.Count = 1 Then
Addends = Split(Replace(Rng.Value, "-", "+-"), "+")
For X = 0 To UBound(Addends)
For Y = 1 To Len(Addends(X))
If Mid$(Addends(X), Y, 1) Like "[!0-9 ./-]" Then
Addends(X) = Left$(Addends(X), Y - 1)
Do While InStr("/. ", Right(Addends(X), 1)) 0
Addends(X) = Left(Addends(X), Len(Addends(X)) - 1)
Loop
If InStr(Addends(X), "/") Then Addends(X) = FracToDec(Addends(X))
Exit For
End If
Next
SumCharacters = SumCharacters + CDbl(Addends(X))
Next
End If
End Function

Function FracToDec(ByVal Fraction As String) As Double
Dim Blank As Integer
Dim Slash As Integer
Dim CharPosition As Integer
Dim WholeNumber As Integer
Dim Numerator As Integer
Dim Denominator As Integer
'Remove leading and trailing blanks
Fraction = Trim$(Fraction)
'Collapse all multiple blanks to a single blank
CharPosition = InStr(Fraction, " ")
Do While CharPosition
Fraction = Left$(Fraction, CharPosition) & _
Mid$(Fraction, CharPosition + 2)
CharPosition = InStr(Fraction, " ")
Loop
'Remove any space character after the slash
CharPosition = InStr(Fraction, "/ ")
If CharPosition Then
Fraction = Left$(Fraction, CharPosition) & _
Mid$(Fraction, CharPosition + 2)
End If
'Remove any space character in front of the slash
CharPosition = InStr(Fraction, " /")
If CharPosition Then
Fraction = Left$(Fraction, CharPosition - 1) & _
Mid$(Fraction, CharPosition + 1)
End If
'Locate the blank and/or slash
Blank = InStr(Fraction, " ")
Slash = InStr(Fraction, "/")
'The Fraction argument can't have characters other than
'blanks, slashes, digits and it can only have one blank
'and/or one slash.
If Fraction Like "*[! /0-9]*" Or _
InStr(Blank + 1, Fraction, " ") Or _
InStr(Slash + 1, Fraction, "/") Then
MsgBox "Error -- Improperly formed expression"
'The Fraction argument is now in one of these formats
'where # stands for one or more digits: #, # #/# or #/#
Else
'There is no slash (Format: #)
If Slash = 0 Then
FracToDec = Val(Fraction)
'There is a slash, but no blank (Format: #/#)
ElseIf Blank = 0 Then
FracToDec = Val(Left$(Fraction, Slash - 1)) / _
Val(Mid$(Fraction, Slash + 1))
'There are both a slash and a blank (Format: # #/#)
Else
FracToDec = Val(Left$(Fraction, Blank - 1)) + _
Val(Mid$(Fraction, Blank + 1, _
Slash - Blank - 1)) / _
Val(Mid$(Fraction, Slash + 1))
End If
End If
End Function

Damn! Minor bug fix. Use this code instead of what I posted previously...

Function SumCharacters(Rng As Range) As Double
Dim X As Long
Dim Y As Long
Dim Addends() As String
If Rng.Count = 1 Then
Addends = Split(Replace(Rng.Value, "-", "+-"), "+")
For X = 0 To UBound(Addends)
For Y = 1 To Len(Addends(X))
If Mid$(Addends(X), Y, 1) Like "[!0-9 ./-]" Then
Addends(X) = Left$(Addends(X), Y - 1)
Do While Addends(X) "" And InStr("/. ", _
Right(Addends(X), 1)) 0
Addends(X) = Left(Addends(X), Len(Addends(X)) - 1)
Loop
Exit For
End If
Next
If InStr(Addends(X), "/") Then Addends(X) = FracToDec(Addends(X))
If Not IsNumeric(Addends(X)) Then Addends(X) = 0
SumCharacters = SumCharacters + CDbl(Addends(X))
Next
End If
End Function

Function FracToDec(ByVal Fraction As String) As Double
Dim Blank As Integer
Dim Slash As Integer
Dim CharPosition As Integer
Dim WholeNumber As Integer
Dim Numerator As Integer
Dim Denominator As Integer
'Remove leading and trailing blanks
Fraction = Trim$(Fraction)
'Collapse all multiple blanks to a single blank
CharPosition = InStr(Fraction, " ")
Do While CharPosition
Fraction = Left$(Fraction, CharPosition) & _
Mid$(Fraction, CharPosition + 2)
CharPosition = InStr(Fraction, " ")
Loop
'Remove any space character after the slash
CharPosition = InStr(Fraction, "/ ")
If CharPosition Then
Fraction = Left$(Fraction, CharPosition) & _
Mid$(Fraction, CharPosition + 2)
End If
'Remove any space character in front of the slash
CharPosition = InStr(Fraction, " /")
If CharPosition Then
Fraction = Left$(Fraction, CharPosition - 1) & _
Mid$(Fraction, CharPosition + 1)
End If
'Locate the blank and/or slash
Blank = InStr(Fraction, " ")
Slash = InStr(Fraction, "/")
'The Fraction argument can't have characters other than
'blanks, slashes, digits and it can only have one blank
'and/or one slash.
If Fraction Like "*[! /0-9]*" Or _
InStr(Blank + 1, Fraction, " ") Or _
InStr(Slash + 1, Fraction, "/") Then
MsgBox "Error -- Improperly formed expression"
'The Fraction argument is now in one of these formats
'where # stands for one or more digits: #, # #/# or #/#
Else
'There is no slash (Format: #)
If Slash = 0 Then
FracToDec = Val(Fraction)
'There is a slash, but no blank (Format: #/#)
ElseIf Blank = 0 Then
FracToDec = Val(Left$(Fraction, Slash - 1)) / _
Val(Mid$(Fraction, Slash + 1))
'There are both a slash and a blank (Format: # #/#)
Else
FracToDec = Val(Left$(Fraction, Blank - 1)) + _
Val(Mid$(Fraction, Blank + 1, _
Slash - Blank - 1)) / _
Val(Mid$(Fraction, Slash + 1))
End If
End If
End Function


Rick

Now that is tight code... Nice!

Just out of curiosity, can you modify it to handle text strings where a decimal point, slash and/or a plus/minus sign appears in the non-numeric part of each addend? I'm thinking of an aberrant text string something like this (I just posted a revision to my function which now can handle such constructions)...

88 1/2+abcd-4+6../ grn-5.5 /. / wht+88 1/2//.//grn

I made an attempt by changing your Pattern to this...

re.Pattern = "[^0-9/. +-]"

but my Regular Expression construction abilities are way too rusty (I think that still lets in too many symbols). It would need to allow only the first decimal point, slash, plus/minus sign and maybe space for each grouping of addends while rejecting all other occurrences of them within each addend.

Rick


"Ron Rosenfeld" wrote in message ...
On Wed, 9 Jan 2008 07:16:59 -0800 (PST), wrote:

Thanx, you guys are great! Worked fine after tweaking security
settings.

One last question, sometimes the addends are fractions such as:

88 1/2grn+3wht+88 1/2grn

How do you tweak the code to see these?

Here's a bit shorter routine that should do that:

==============================================
Option Explicit
Function SumNums(str As String) As Double
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[A-Za-z]"
SumNums = Evaluate(re.Replace(str, ""))
End Function
==============================================
--ron

On Wed, 9 Jan 2008 16:17:47 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Here is a modification to the code I just posted which should survive most (if not all) combinations of characters following a valid number in each of the addends making up your text. That is, a text string like this should be evaluated fine....

88 1/2../ grn-5.5 /. / wht+88 1/2//.//grn

I'm not sure if your text could ever be this malformed, but the code will function correctly it if it can be. Use the same instructions for implementing the code below my signature as I gave you in my previous posting.

Wow, that's pretty malformed.

Here's a bit shorter routine that should do the same, though, even with this
degree of malformation:

=================================
Option Explicit
Function SumNums(str As String) As Double
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "([A-Za-z])|(\D[./]\D)"
SumNums = Evaluate(re.Replace(str, ""))
End Function
===================================
--ron

Here is a modification to the code I just posted which should survive most (if not all) combinations of characters following a valid number in each of the addends making up your text. That is, a text string like this should be evaluated fine....

88 1/2../ grn-5.5 /. / wht+88 1/2//.//grn

I'm not sure if your text could ever be this malformed, but the code will function correctly it if it can be. Use the same instructions for implementing the code below my signature as I gave you in my previous posting.

Wow, that's pretty malformed.

Yeah, I knowg... but I found the text the OP asked us to evaluate to be malformed to begin with, so we are only talking of degree.

Here's a bit shorter routine that should do the same, though, even with this
degree of malformation:

=================================
Option Explicit
Function SumNums(str As String) As Double
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "([A-Za-z])|(\D[./]\D)"
SumNums = Evaluate(re.Replace(str, ""))
End Function
===================================

I tried to modify your Pattern string (see my latest post to you in this sub-thread), but that is not the direction I was heading in when I gave up.g There is no question that in certain circumstance, such as this OP's request, Regular Expressions truly rule! Nice going!!!

Rick

On Wed, 9 Jan 2008 17:58:20 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Here is a modification to the code I just posted which should survive most (if not all) combinations of characters following a valid number in each of the addends making up your text. That is, a text string like this should be evaluated fine....

88 1/2../ grn-5.5 /. / wht+88 1/2//.//grn

I'm not sure if your text could ever be this malformed, but the code will function correctly it if it can be. Use the same instructions for implementing the code below my signature as I gave you in my previous posting.

Wow, that's pretty malformed.

Yeah, I knowg... but I found the text the OP asked us to evaluate to be malformed to begin with, so we are only talking of degree.

Here's a bit shorter routine that should do the same, though, even with this
degree of malformation:

=================================
Option Explicit
Function SumNums(str As String) As Double
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "([A-Za-z])|(\D[./]\D)"
SumNums = Evaluate(re.Replace(str, ""))
End Function
===================================

I tried to modify your Pattern string (see my latest post to you in this sub-thread), but that is not the direction I was heading in when I gave up.g There is no question that in certain circumstance, such as this OP's request, Regular Expressions truly rule! Nice going!!!

Rick

Thank you.

A problem with the malformations is that we don't really know how to interpret
it. I took the point of view that the "/" needed to be part of a fraction, so
I could test to make sure it was surrounded by digits. The same is true of the
".". But maybe a standalone "/" should be interpreted as a "divide" operator.
What about other standalone possible operators?

I also make the assumption that a "-" is a negation or subtraction, even if it
stands alone. But for consistency with your routine, I did not make that
assumption with the "/"

Anyway, it's an interesting exercise.

--ron

In line....

A problem with the malformations is that we don't really know how to interpret
it. I took the point of view that the "/" needed to be part of a fraction, so
I could test to make sure it was surrounded by digits. The same is true of the
".". But maybe a standalone "/" should be interpreted as a "divide" operator.
What about other standalone possible operators?

I also make the assumption that a "-" is a negation or subtraction, even if it
stands alone. But for consistency with your routine, I did not make that
assumption with the "/"

Yes, there is no way of knowing what the OP wanted given the original posting. My guess from what he posted was that he only needed to handle addition; but I figured it was easy enough to add subtraction to the routine I came up with that I included it too. Part of the reason for doing that is my experience with too many postings asking a question and then, after providing a solution, having the OP come back and tell us that was just a simplified example, here is what I actually need (why do so many posters do that anyway?).

Anyway, it's an interesting exercise.

Yes, for sure. And your posted solution is probably going to make me dig out my Regular Expression books (I know, I said that last time, didn't I?) and try relearn them. If I remember correctly, you said the Regular Expression implementation from VBScript is what you use.

Rick

On Wed, 9 Jan 2008 19:58:31 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

If I remember correctly, you said the Regular Expression implementation from VBScript is what you use.

Yes, that's correct. It is virtually the same as the Javascript
implementation. Take a look he
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx

On the MS site, if you select Regular Expressions under VBScript, the links
take you to the JScript documentation.
--ron

that was just a simplified example, here is what I actually need
(why do so many posters do that anyway?).

That's the proverbial $64,000 question!

Solve that dilemma and you can become fabulously rich.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
In line....

A problem with the malformations is that we don't really know how to
interpret
it. I took the point of view that the "/" needed to be part of a
fraction, so
I could test to make sure it was surrounded by digits. The same is true
of the
".". But maybe a standalone "/" should be interpreted as a "divide"
operator.
What about other standalone possible operators?

I also make the assumption that a "-" is a negation or subtraction, even
if it
stands alone. But for consistency with your routine, I did not make that
assumption with the "/"

Yes, there is no way of knowing what the OP wanted given the original
posting. My guess from what he posted was that he only needed to handle
addition; but I figured it was easy enough to add subtraction to the routine
I came up with that I included it too. Part of the reason for doing that is
my experience with too many postings asking a question and then, after
providing a solution, having the OP come back and tell us that was just a
simplified example, here is what I actually need (why do so many posters do
that anyway?).

Anyway, it's an interesting exercise.

Yes, for sure. And your posted solution is probably going to make me dig out
my Regular Expression books (I know, I said that last time, didn't I?) and
try relearn them. If I remember correctly, you said the Regular Expression
implementation from VBScript is what you use.

Rick