Trying to sum numbers contained in memo field

[Gerrit1966]

I came across a function to sum numbers in this thread:

http://bytes.com/topic/access/answers/899716-how-extract-numbers-memo-field-sum-them

I tried to use it, and it works fine for numbers without 00.
However, when summing 1500 + 400 if gives 19 as answer.
Please help me to use the function correctly.

[TheSmileyCoder]

Here is a variation of the code from the other post. It will not work on doubles, and there may be other bugs. Use at your own risk :)

Public Function TSCf_CalcTotals(varMEMO As Variant) As Variant
Dim lngLenOfString As Long
Dim lngTotal As Long
Dim strMemo As String
'Check for empty memo
   If varMEMO & "" = "" Then
      'Memo is empty/null, return null
      TSCf_CalcTotals = Null
      Exit Function
   End If

'Convert variant to memo
   strMemo = CStr(varMEMO)

lngLenOfString = Len(strMemo)
Dim l As Long
Dim i As Integer 'Store currentsub string length
l = 1
i = 1
Do
   'Check substring to see if it is numeric
   If IsNumeric(Mid(strMemo, l, i)) Then
      'current substring is numeric
      i = i + 1
      Do Until Not IsNumeric(Mid(strMemo, l, i)) Or l + i > lngLenOfString + 1
         i = i + 1
      Loop
      'substring is no longer numeric.
         'Backtrack 1 strep
            i = i - 1
         'convert substring to long, and add to total
            lngTotal = lngTotal + CLng(Mid(strMemo, l, i))
            l = l + i
   Else
      'substring not numeric, move to next
      l = l + 1
   End If
   'Reset i
   i = 1
   
Loop Until l > lngLenOfString
TSCf_CalcTotals = lngTotal

 
End Function

[Gerrit1966]

Thanks, this works perfect.