You could set up a User Defined Function to replace all characters with
a + in the string, and then pass this to the Evaluate function. If you
are likely to have two or more letters next to each other then the UDF
would have to detect for this.

I'm about to go out now - I'm sure others will be able to expand on
this.

Hope this helps.

Pete

try this. Change the range to suit>place in a REGULAR module>
then just use as a regular function =gn("orange") or =gn("apple") NOT appleS

Function gn(y)
application.volatile 'may not be necessary
Dim mn As Long
For Each c In Range("c2:c22")
If InStr(c, y) > 0 Then
mn = mn + Val(Left(c, InStr(c, " ")))
End If
Next
gn = mn
End Function

One method would be to download and install Longre's free morefunc.xll add-in
from http://xcell05.free.fr

Then use this **array** formula:

=EVAL(MCONCAT(REGEX.MID(A1,"\d+",ROW(
INDIRECT("1:"&REGEX.COUNT(A1,"\d+")))),"+"))

To enter an **array** formula, after copying or typing the formula into the
formula bar, hold down <ctrl><shift> while hitting <enter>.  Excel will place
braces {...} around the formula.

--ron

Here's a Regular Expression example on some test data in A1:A3.  Output is
to B1:B3.
Adjust as necessary.

Sub Demo()
   Dim RE As Object
   Dim Cell As Range
   Dim S As String
   Const Sp As String = " "
   Const P As String = "+"

   Set RE = CreateObject("VBScript.RegExp")
   RE.Global = True
   RE.Pattern = "\D+"

'// Test Data
 [A1] = "D2K13 V0F4"
 [A2] = " Y2"
 [A3] = "H10T4 "

 For Each Cell In [A1:A3].Cells
   If RE.test(Cell) Then
     Cell(1, 2) = Evaluate(Replace(Trim(RE.Replace(Cell, Sp)), Sp, P))
   End If
 Next Cell
End Sub

Hopefully this will work for your data.

Does this ,though very elementary way of coding, work in your case?

D2K13 V0F4 in A1, and put a formula =myadd(A1) in B1 , then B1 will be 19.
myadd is like this

Function myadd(ByVal s As String) As Variant
Dim t As String, v As String
If s = "" Then myadd = "": Exit Function
Do While (s <> "")
   t = Mid(s, 1, 1)
   If IsNumeric(t) Then
       v = v & t
   Else
       myadd = myadd + Val(v)
       v = ""
   End If
   s = Mid(s, 2)
Loop
myadd = myadd + Val(v)
End Function

keizi

=SUM(IF(MMULT(--ISERR(-MID(SUBSTITUTE(A1,"+","|")&"|",(ROW(1:990)-1)/10+
{0,1,1},MOD(ROW(1:990)-1,10)+{2,1,2})),{1;2;4})=5,--MID(A1,(ROW(1:990)-1)/10+
1,MOD(ROW(1:990)-1,10)+1)))

This was published in a prior post - it works on strings up to 99
characters and numbers up to 10 digits by extracting numeric portions
and summing them up.

Billy,

Function sumnums(mycell)
c = Len(mycell)
For i = 1 To c
totals = totals + Val(Mid(mycell, i, 1))
Next
sumnums = totals
End Function

Don