J
jbruce911
How do you add numbers in a cell with letters mixed?
such as:
160grn+4wht+17grn
Thanx in advance for your help!
such as:
160grn+4wht+17grn
Thanx in advance for your help!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
ryguy7272
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--
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--
R
Rick Rothstein \(MVP - VB\)
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
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
J
jbruce911
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?
R
Rick Rothstein \(MVP - VB\)
You UDF adds each digit individually... looking at the structure of the
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
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
G
Gord Dibben
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 Insert>Module
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
workbook.
Alt + F11 to open VBEditor.
CTRL + r to open Project Explorer.
Right-click on your workbook/project and Insert>Module
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
G
Gord Dibben
Whoops
Apologies Rick.......was not paying attention and missed your response, which I
duplicated later.
Gord
Apologies Rick.......was not paying attention and missed your response, which I
duplicated later.
Gord
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
R
Rick Rothstein \(MVP - VB\)
No apologies are necessary as I am concerned my friend.
Rick
Rick
Gord Dibben said:Whoops
Apologies Rick.......was not paying attention and missed your response,
which I
duplicated later.
Gord
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
J
jbruce911
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 FunctionI'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?
R
Rick Rothstein \(MVP - VB\)
One last question, sometimes the addends are fractions such as:
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
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
R
Ron Rosenfeld
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
R
Rick Rothstein \(MVP - VB\)
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
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
R
Rick Rothstein \(MVP - VB\)
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
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
R
Rick Rothstein \(MVP - VB\)
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
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 said: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
R
Ron Rosenfeld
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
R
Rick Rothstein \(MVP - VB\)
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....
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
Wow, that's pretty malformed.
Yeah said: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
R
Ron Rosenfeld
Wow, that's pretty malformed.
Yeah said: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
R
Rick Rothstein \(MVP - VB\)
In line....
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?).
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
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
R
Ron Rosenfeld
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 here:
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
T
T. Valko
that was just a simplified example, here is what I actually need
That's the proverbial $64,000 question!
Solve that dilemma and you can become fabulously rich.
--
Biff
Microsoft Excel MVP
message In line....
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?).
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
(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
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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.