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

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?

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?