Create an UDF to get Odd, Even and Prime Numbers from a string

[Luthius]

Guys
I would like your support on how to extract from a string the numbers and count the quantity of
Odd Numbers, Even Numbers, Prime Numbers.

The string can come this way with "-" as delimiter.
1-2-3-4-5-6-7-8-9-10-11-12-13-14-15
3-6-7-11-173-14

[COLOR=#0000cd]Public Function[/COLOR] MyUDF(strNumbers [COLOR=#0000cd]As String[/COLOR], returnType [COLOR=#0000cd]As Integer[/COLOR])[COLOR=#008000] 
  
   '1=Odd Numbers[/COLOR]
[COLOR=#008000]    '2=Even Numbers[/COLOR]
[COLOR=#008000]    '3=Prime Numbers[/COLOR]
    [COLOR=#0000cd]Select Case[/COLOR] returnType
    [COLOR=#0000cd]Case[/COLOR] 1
[COLOR=#008000]        'Will return the quantity of Odd numbers[/COLOR]
    [COLOR=#0000cd]Case[/COLOR] 2
[COLOR=#008000]        'Will return the quantity of Even numbers[/COLOR]
    [COLOR=#0000cd]Case[/COLOR] 3
[COLOR=#008000]        'Will return the quantity of Prime numbers[/COLOR]
[COLOR=#0000cd]    End Select[/COLOR]
    
[COLOR=#0000cd]End Function[/COLOR]

 

[Luthius]

Thank you guys, just a last question as an additional item to this.
Is possible increment to this UDF a way to count the quantity of sequential numbers?

Example below that the higher quantity of number in sequence is 7
01-02-05-08-09-10-11-12-13-15-17-18-19-20-25

Another example
05 Numbers in sequence.
01-02-05-07-10-11-14-15-16-19-21-22-23-24-25

 

[kweaver]

In your first example, don't you mean the 15 should be a 14?

 

[Luthius]

Yes you are right.
6 numbers
01-02-05-08-09-10-11-12-13-15-17-18-19-20-25

05 Numbers in sequence.
01-02-05-07-10-11-14-15-16-19-21-22-23-24-25

 

[lrobbo314]

How about this?

Public Function MyUDF(strNumbers As String, returnType As Integer)
Dim SP() As String: SP = Split(strNumbers, "-")
Dim Total As Integer
   '1=Odd Numbers
    '2=Even Numbers
    '3=Prime Numbers
    
For i = LBound(SP) To UBound(SP)
    Select Case returnType
    Case 1
        If Int(SP(i)) Mod 2 = 1 Then Total = Total + 1
    Case 2
        If Int(SP(i)) Mod 2 = 0 Then Total = Total + 1
    Case 3
        If ISPRIME(Int(SP(i))) Then Total = Total + 1
    Case 4
        MyUDF = SEQUENCE(SP)
        Exit Function
    End Select
Next i

MyUDF = Total
    
End Function

Function SEQUENCE(SP() As String) As Long
Dim Total As Long: Total = 1
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim High As Long

For i = LBound(SP) + 1 To UBound(SP)
    If Int(SP(i)) - 1 = Int(SP(i - 1)) Then
        Total = Total + 1
    Else
        If Total > 1 Then AL.Add Total
        Total = 1
    End If
Next i

AL.Add Total

For j = 0 To AL.Count - 1
    If AL(j) > High Then High = AL(j)
Next j

SEQUENCE = High

End Function

Function ISPRIME(Num As Double) As Boolean
    Dim i As Double
    If Num = 1 Then ISPRIME = False: Exit Function
    If Num = 2 Then ISPRIME = True: Exit Function
    If Int(Num / 2) = (Num / 2) Then
        Exit Function
        Else
        For i = 3 To Sqr(Num) Step 2
            If Int(Num / i) = (Num / i) Then
                Exit Function
            End If
        Next i
    End If
    ISPRIME = True
End Function

 

[Luthius]

I tested and its giving an error #Value
It is one single string inside a cell like this
01-02-05-08-09-10-11-12-13-15-17-18-19-20-25

 

[lrobbo314]

Works fine for me. I'm calling the function like this

=MyUDF(A1,4)

 

[kweaver]

Nicely done, Irobbo314

 

[lrobbo314]

Thanks!

 

[Luthius]

Thank you very much. It works perfectly...
Awesome!
Thanks for your time

 

[Rick Rothstein]

Yes you are right.
6 numbers
01-02-05-08-09-10-11-12-13-15-17-18-19-20-25

05 Numbers in sequence.
01-02-05-07-10-11-14-15-16-19-21-22-23-24-25

Just out of curiosity, are these examples representative of your actual values?

What is the largest number that could appear between delimiters?