You could use a User Defined Function. There's not much error checking in this so it might be unhappy with data that looks less like the example.
Option Explicit
Function fSumCell(ByRef rng As Range)
Dim vA, vE, vT
Dim dTotal As Double
vA = Split(rng, ";")
For Each vE In vA
If Right(vE, 1) = ";" Then vE = Left(vE, Len(vE) - 1)
If Right(vE, 1) = ")" Then vE = Left(vE, Len(vE) - 1)
vE = IIf(InStr(1, vE, "("), CDbl(Split(vE, "(")(UBound(Split(vE, "(")))), "")
dTotal = dTotal + vE
'Debug.Print vE
Next 'vE
'Debug.Print dTotal
fSumCell = dTotal
End Function
Sub sTest_fSumCell()
fSumCell Range("A1")
End Sub
Regards, TMS
Bookmarks