Guys I found a code to extract numeric, decimal or negative values from a
alphanumeric value. It works great, my problem is that I want to use it for
an array in a sumproduct.

For example,

AL 8
AL 2
NI 3
AL 2

I want to use =SUMPRODUCT(LEFT(A1:A4,2)="AL",(ExtractNumber(A1:A 4,,TRUE)))
but I'm getting a #value error because the extract number UDF doens't allow
me to take an array.

Here is the code for your reference:

Function ExtractNumber(rCell As Range, _
Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As
Double

Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String, strNeg As String, strDec As String
Dim lNum As String
Dim vVal, vVal2

''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgrid.com

'Extracts a number from a cell containing text and numbers.
''''''''''''''''''''''''''''''''''''''''''
sText = rCell
If Take_decimal = True And Take_negative = True Then
strNeg = "-" 'Negative Sign MUST be before 1st number.
strDec = "."
ElseIf Take_decimal = True And Take_negative = False Then
strNeg = vbNullString
strDec = "."
ElseIf Take_decimal = False And Take_negative = True Then
strNeg = "-"
strDec = vbNullString
End If
iLoop = Len(sText)

For iCount = iLoop To 1 Step -1
vVal = Mid(sText, iCount, 1)


If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
If IsNumeric(lNum) Then
If CDbl(lNum) < 0 Then Exit For
Else
lNum = Replace(lNum, Left(lNum, 1), "", , 1)
End If
End If

If i = 1 And lNum < vbNullString Then lNum = CDbl(Mid(lNum,
1, 1))
Next iCount


ExtractNumber = CDbl(lNum)

End Function