How do you add numbers in a cell with letters mixed?
such as:

160grn+4wht+17grn

Thanx in advance for your help!

Try this UDF:

Function SumCharacters(Rng As Range) As Long

Dim i As Long
Dim s As String
Dim lSum As Long
Dim mycell As Range

lSum = 0
For Each mycell In Rng.Cells
For i = 1 To Len(mycell.Value)
s = Mid(mycell.Value, i, 1) 'mycell.text if it's formatted
If IsNumeric(s) Then
lSum = lSum + s
End If
Next i
Next mycell

SumCharacters = lSum
End Function



Regards,
Ryan--


--
RyGuy


" wrote:

How do you add numbers in a cell with letters mixed?
such as:

160grn+4wht+17grn

Thanx in advance for your help!

You UDF adds each digit individually... looking at the structure of the
string, it seems more likely to me that the OP want to throw away the
letters and add the numbers with plus signs between them. I'm thinking a UDF
more like this would be what the OP wants...

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)
Exit For
End If
Next
SumCharacters = SumCharacters + CDbl(Addends(X))
Next
End If
End Function

Note: I set this UDF up so it would calculate the number whether a plus or
minus sign separated them.

Rick


"ryguy7272" wrote in message
...
Try this UDF:

Function SumCharacters(Rng As Range) As Long

Dim i As Long
Dim s As String
Dim lSum As Long
Dim mycell As Range

lSum = 0
For Each mycell In Rng.Cells
For i = 1 To Len(mycell.Value)
s = Mid(mycell.Value, i, 1) 'mycell.text if it's formatted
If IsNumeric(s) Then
lSum = lSum + s
End If
Next i
Next mycell

SumCharacters = lSum
End Function



Regards,
Ryan--


--
RyGuy


" wrote:

How do you add numbers in a cell with letters mixed?
such as:

160grn+4wht+17grn

Thanx in advance for your help!

On Jan 8, 12:42 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
You UDF adds each digit individually... looking at the structure of the
string, it seems more likely to me that the OP want to throw away the
letters and add the numbers with plus signs between them. I'm thinking a UDF
more like this would be what the OP wants...

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)
Exit For
End If
Next
SumCharacters = SumCharacters + CDbl(Addends(X))
Next
End If
End Function


I'm new to functions. I havent tried this because i need to make sure
i got it right.

I need this so i can print in different cell, (ie- "3wht+170grn" in
cell C1 which would show as "173" in cell C2.)

Anything I need to change in this to do that, and how do I add the
function? Just add it in with an equal sign in front?

I feel so stupid, and appreciate your time?

You UDF adds each digit individually... looking at the structure of the
string, it seems more likely to me that the OP want to throw away the
letters and add the numbers with plus signs between them. I'm thinking a
UDF
more like this would be what the OP wants...

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)
Exit For
End If
Next
SumCharacters = SumCharacters + CDbl(Addends(X))
Next
End If
End Function

I'm new to functions. I havent tried this because i need to make sure
i got it right.

I need this so i can print in different cell, (ie- "3wht+170grn" in
cell C1 which would show as "173" in cell C2.)

Anything I need to change in this to do that, and how do I add the
function? Just add it in with an equal sign in front?

From the spreadsheet, press Alt+F11 to get into the VBA editor. Then click
on Insert/Module (that is Module, not Class Module) in the menu bar.
Copy/Paste the function I posted into the code window that opened up when
you click the Insert/Module menu item. That's it; you can know use the
SumCharacters function as if it were a built-in Excel function. If the text
to add is in C1 as you posted, then put this in C2...

=SumCharacters(C1)

and it should give you 173 as the answer. By the way, I used the name for
the function that Ryan used, but that is not a requirement. If you wanted to
call the function by a different name, then just change the 3 occurrences of
SumCharacters in my code to whatever name you want to use.

Rick

The function code itself must be copied/pasted into a general module in the
workbook.

Alt + F11 to open VBEditor.

CTRL + r to open Project Explorer.

Right-click on your workbook/project and InsertModule

Paste the UDF into that module.

Alt + q to return to Excel

Then in any cell enter =sumcharacters(cellref) where cellref is the cell
with 3wht+170grn


Gord Dibben MS Excel MVP

On Tue, 8 Jan 2008 13:48:04 -0800 (PST), wrote:

I'm new to functions. I havent tried this because i need to make sure
i got it right.

I need this so i can print in different cell, (ie- "3wht+170grn" in
cell C1 which would show as "173" in cell C2.)

Anything I need to change in this to do that, and how do I add the
function? Just add it in with an equal sign in front?

I feel so stupid, and appreciate your time?

Whoops

Apologies Rick.......was not paying attention and missed your response, which I
duplicated later.


Gord

On Tue, 8 Jan 2008 17:13:54 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

You UDF adds each digit individually... looking at the structure of the
string, it seems more likely to me that the OP want to throw away the
letters and add the numbers with plus signs between them. I'm thinking a
UDF
more like this would be what the OP wants...

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)
Exit For
End If
Next
SumCharacters = SumCharacters + CDbl(Addends(X))
Next
End If
End Function

I'm new to functions. I havent tried this because i need to make sure
i got it right.

I need this so i can print in different cell, (ie- "3wht+170grn" in
cell C1 which would show as "173" in cell C2.)

Anything I need to change in this to do that, and how do I add the
function? Just add it in with an equal sign in front?

From the spreadsheet, press Alt+F11 to get into the VBA editor. Then click
on Insert/Module (that is Module, not Class Module) in the menu bar.
Copy/Paste the function I posted into the code window that opened up when
you click the Insert/Module menu item. That's it; you can know use the
SumCharacters function as if it were a built-in Excel function. If the text
to add is in C1 as you posted, then put this in C2...

=SumCharacters(C1)

and it should give you 173 as the answer. By the way, I used the name for
the function that Ryan used, but that is not a requirement. If you wanted to
call the function by a different name, then just change the 3 occurrences of
SumCharacters in my code to whatever name you want to use.

Rick

No apologies are necessary as I am concerned my friend.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Whoops

Apologies Rick.......was not paying attention and missed your response,
which I
duplicated later.


Gord

On Tue, 8 Jan 2008 17:13:54 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

You UDF adds each digit individually... looking at the structure of the
string, it seems more likely to me that the OP want to throw away the
letters and add the numbers with plus signs between them. I'm thinking
a
UDF
more like this would be what the OP wants...

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)
Exit For
End If
Next
SumCharacters = SumCharacters + CDbl(Addends(X))
Next
End If
End Function

I'm new to functions. I havent tried this because i need to make sure
i got it right.

I need this so i can print in different cell, (ie- "3wht+170grn" in
cell C1 which would show as "173" in cell C2.)

Anything I need to change in this to do that, and how do I add the
function? Just add it in with an equal sign in front?

From the spreadsheet, press Alt+F11 to get into the VBA editor. Then click
on Insert/Module (that is Module, not Class Module) in the menu bar.
Copy/Paste the function I posted into the code window that opened up when
you click the Insert/Module menu item. That's it; you can know use the
SumCharacters function as if it were a built-in Excel function. If the
text
to add is in C1 as you posted, then put this in C2...

=SumCharacters(C1)

and it should give you 173 as the answer. By the way, I used the name for
the function that Ryan used, but that is not a requirement. If you wanted
to
call the function by a different name, then just change the 3 occurrences
of
SumCharacters in my code to whatever name you want to use.

Rick

On Jan 8, 5:13*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
You UDF adds each digit individually... looking at the structure of the
string, it seems more likely to me that the OP want to throw away the
letters and add the numbers with plus signs between them. I'm thinking a
UDF
more like this would be what the OP wants...

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)
* * * * * Exit For
* * * * End If
* * * Next
* * * SumCharacters = SumCharacters + CDbl(Addends(X))
* * Next
* End If
End Function

I'm new to functions. I havent tried this because i need to make sure
i got it right.

I need this so i can print in different cell, (ie- "3wht+170grn" in
cell C1 which would show as "173" in cell C2.)

Anything I need to change in this to do that, and how do I add the
function? Just add it in with an equal sign in front?

From the spreadsheet, press Alt+F11 to get into the VBA editor. Then click
on Insert/Module (that is Module, not Class Module) in the menu bar.
Copy/Paste the function I posted into the code window that opened up when
you click the Insert/Module menu item. That's it; you can know use the
SumCharacters function as if it were a built-in Excel function. If the text
to add is in C1 as you posted, then put this in C2...

=SumCharacters(C1)

and it should give you 173 as the answer. By the way, I used the name for
the function that Ryan used, but that is not a requirement. If you wanted to
call the function by a different name, then just change the 3 occurrences of
SumCharacters in my code to whatever name you want to use.

Rick- Hide quoted text -

- Show quoted text -

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?

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?

Delete the function I gave you earlier and Copy/Paste the code following my signature in its place. The function name you call has not changed, so your existing spreadsheet formulas calls should be fine just as they are. What I did is add a second function to convert numbers of the form you showed your mixed numbers to be in and then modified my original function to allow the space and slash characters to be incorporated into the numerical parts of the string. Give it a try and see if it works for you.

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

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 know<g... 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 know<g... 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

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

Thanks for the link... I just printed out the pages.

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

Interesting.

Rick

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.

LOL

This was not as great a problem over in the compiled VB newsgroups as it seems to be in the Excel newsgroups (but it does happen over there too).

Rick