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?