Impressive...I think it gives rise to the quote "give me a problem and I will
smash it into the ground"

"Bob Phillips" wrote:

Got it. A nice simple formula :-)

=SUMPRODUCT(--MID(A1,FIND("~",SUBSTITUTE(";"&A1&";",";","~",ROW( INDIRECT("1:
"&LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+1)))),FIND("~",SUBSTITU TE(";"&A1&";",";
","~",ROW(INDIRECT("2:"&LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+2))))-FIND("~",SU
BSTITUTE(";"&A1&";",";","~",ROW(INDIRECT("1:"&LEN( A1)-LEN(SUBSTITUTE(A1,";",
""))+1))))-2))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Andy,

There must be a worksheet function way of doing this, but it's simple
with
a UDF

Function CountNum(rng As Range, Optional delimiter As String = ",")
Dim iPos As Long
Dim istart As Long
Dim tmp

If rng.Cells.Count 1 Then
CountNum = CVErr(xlErrRef)
Exit Function
End If
istart = 1
For iPos = 1 To Len(rng.Value)
If Mid(rng.Value, iPos, 1) = delimiter Then
tmp = tmp + CDbl(Mid(rng.Value, istart, iPos - istart))
istart = iPos + 1
End If
Next iPos
If Right(rng.Value, 1) < delimiter Then
tmp = tmp + CDbl(Mid(rng.Value, istart, iPos - istart))
End If
CountNum = tmp
End Function

Call it like so

=countnum(AA1,";")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andy Brown" wrote in message
...
If I have "0.120;0.140;0.200" in a cell, is it possible to get the sum
of
the values with a formula (NB: not code)?

=INDIRECT("="&SUBSTITUTE(A1,";","+")) just equals #REF!

TIA,
Andy