On Apr 5, 4:40*am, Ron Rosenfeld wrote:
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- Hide quoted text -

- Show quoted text -

Hi,

If using a UDF, the VBA Split function would be perfect.

Try:

Function SplitStr(InputS As String, Optional Delim As String = ",") As
Variant
Dim a As Variant, b As Variant, i As Long
a = Split(InputS, Delim)
For i = LBound(a) To UBound(a)
b = Split(InputS, a(i))
If InStr(SplitStr, " (" & UBound(b) & ") " & a(i)) = 0 Then
SplitStr = SplitStr & ", (" & UBound(b) & ") " & a(i)
End If
Next
SplitStr = Right(SplitStr, Len(SplitStr) - 2)
End Function

Use as: =SplitStr(A1,",")

If you wanted them in order, you would just have to sort the array "a"
first.

Cheers,
Ivan.