On Fri, 04 Apr 2008 13:07:53 -0400, Ron Rosenfeld
wrote:

On Fri, 4 Apr 2008 08:20:01 -0700, Tabby
wrote:

I have a worksheet that has repeating numbers in one cell.
Example: 1204,1205,1206,1205,1204

Is there a formula that will total the repeating numbers? i.e. (2) 1204,
(2) 1205?

You could write a short UDF to do that. This will add the numbers in a string
that consists of comma-separated numbers.

To USE this UDF, enter a formula like:
=AddCSN(cell_ref)

To ENTER the code, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Enjoy. Note there is no error-checking in this code -- illegal values will
give a #VALUE! error.

============================
Function AddCSN(str As String) As Double
AddCSN = Evaluate(Replace(str, ",", "+"))
End Function
===============================
--ron

I just read your comment that you might have up to 80 numbers in each cell. The
above will only work for strings up to 255 characters in length, so I will
suggest this instead, which does not have that limitation:

===============================
Option Explicit
Function AddCSN(str As String) As Double
Dim i As Long
Dim sTemp
sTemp = Split(str, ",")
For i = 0 To UBound(sTemp)
AddCSN = AddCSN + sTemp(i)
Next i
End Function
==============================
--ron