Dear Francis

Suppose you have the below data in cell A1 (as a single line)
** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

Try this UDF which will give you the total..

=getvaluefromstring(A1)


Launch VBE using Alt+F11. Insert a module and save..

Function GetValuefromString(varTemp As Range) As Currency
Dim intStart As Integer
Dim intPos As Integer
Dim strData As String
strData = varTemp.Text & ":"
intStart = 1
Do
intPos = InStr(intStart, strData, "INC")
If intPos < 0 Then
GetValuefromString = GetValuefromString + Trim(Mid(strData, _
intPos + 3, InStr(intPos, strData, ":") - intPos - 3))
End If
intStart = intPos + 1
Loop While intPos < 0
End Function




If this post helps click Yes
---------------
Jacob Skaria


"Francis" wrote:

A2 contain the following string :

** CLAIMS ** YR2006 NO. 1 INC $959.82: YR2007 NO. 2 INC $30,708.72: YR2008
NO. 2 INC $13,922.14

I want in B2 to add up the No which will give 5 on the above example
and in C2 give me the total amount of 45590.68

Is there a way to do this?

TIA


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another