Sum numbers from text&num array?

[ljubo_gr]

Hello,
i need help of looking an array of data, please Find {0,1,2,3,4,5,6,7,8,9}, and then Sumproduct.

W50	150	s768	Q737
5564	Y325	X7000	65000
K1500	1200	C3300	2500
1200	e700	b100	1000

<tbody>
</tbody>

Result should be: 91,094 units. Text will mostly be single char letter, always in front, from beginning.
THANKS in advance!

 

[ljubo_gr]

heres a UDF

Function sumranges(ParamArray cellranges() As Variant)
Dim cell As Range, i As Long, area As Variant
For Each area In cellranges
If TypeName(area) = "Range" Then
For Each cell In area
If Asc(UCase(cell)) > 64 And Asc(UCase(cell)) < 91 Then
i = i + (Mid(cell, 2, 256) + 0)
Else
i = i + cell
End If
Next
End If
Next
sumranges = i
End Function

This UDF not working, I also have empty cells, text2017, 2017text,.. and negative numbers like -2017, so this isn0t working, THANKS BarryL!

 

[ljubo_gr]

Solved, THANKS JohnTopley https://www.excelforum.com/excel-fo...4245-extract-numbers-from-text0000text-3.html

Function Add_Numeric(iRng As Range) As Long

For Each cell In iRng
    l = Len(cell)
    If l = 0 Then GoTo nextcell
    numstr = ""
    Mf = 1
    If InStr(1, cell, "-") Then Mf = -1
    For i = 1 To l
        If IsNumeric(Mid(cell, i, 1)) Then numstr = numstr & Mid(cell, i, 1)
    Next i
    Add_Numeric = Add_Numeric + numstr * Mf
nextcell:
Next cell

End Function