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]

 

[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
    End Select
Next i

MyUDF = Total
    
End Function

Function ISPRIME(Num As Double) As Boolean
    Dim i As Double
    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

 

[kweaver]

I like those 2 functions. I wrote something that does it in the one UDF but it's very much like what you ultimately did.

Function NumTypes(strNumbers As String, ReturnType As Integer)
Dim numbers As Variant
Dim i As Integer, tot As Integer, j As Integer, div As Integer
numbers = Split(strNumbers, "-")
 
 Select Case ReturnType
 
 Case 1  ' odd numbers
 tot = 0
 For i = LBound(numbers) To UBound(numbers)
   If numbers(i) Mod 2 = 1 Then
   tot = tot + 1
   End If
 Next i
NumTypes = tot

  Case 2  ' even numbers
 tot = 0
 For i = LBound(numbers) To UBound(numbers)
   If numbers(i) Mod 2 = 0 Then
   tot = tot + 1
   End If
 Next i
NumTypes = tot

  Case 3  ' prime numbers
 tot = 0
 For i = LBound(numbers) To UBound(numbers)
 div = 0
   For j = 1 To numbers(i)
     If numbers(i) Mod j = 0 Then
       div = div + 1
     End If
   Next j
  If div = 2 Then
  tot = tot + 1
   End If
 Next i
NumTypes = tot

End Select
End Function

 

[lrobbo314]

Had to make an update to the ISPRIME function.

Function ISPRIME(Num As Double) As Boolean
    Dim i As Double
[COLOR=#0000ff]    If Num = 1 Then ISPRIME = False: Exit Function[/COLOR]
[COLOR=#0000ff]    If Num = 2 Then ISPRIME = True: Exit Function[/COLOR]
    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

 

[kweaver]

I ran your original and it doesn't return true for 1 and does return true for 2.

 

[Luthius]

Thank you guys for amazing solutions.
I was just wondering why we dont have all the functions available in VBA.
For odd and even numbers it would be solved if the MOD function was available.

Odd numbers
Application.WorksheetFunction.SUMPRODUCT(MOD(strNumbers,2))

Even Numbers
Application.WorksheetFunction.SUMPRODUCT(--(NOT(MOD(strNumbers,2))))

Thanks

 

[kweaver]

Hi, Luthius...you'll notice we both used the VBA MOD function which takes a left and right argument unlike the Excel formula version. But, it's there.

 

[Luthius]

I saw but is a bit different when using it in a cell. As SUMPRODUCT is an array formula, so when we usi it in a module it doesnt work.

 

[lrobbo314]

Here is a way using Power Query. Add this code to a blank query and name the query 'Primes'. This adds numbers from 1 to 100. That can be adjusted if your numbers go higher. Credit to this forum for the code below.

let
    List.Sequence = (min, max) => List.Generate(() => min, (x) => x <= max, (x) => x + 1),
    FilterComposites = (candidates, value) => List.Select(candidates, (c) => Number.Mod(c, value) <> 0),
    List.Primes = (candidates) => let
        first = List.First(candidates),
        rest = List.Skip(candidates, 1),
        primes = if first = null then {} else {first} & GetPrimes2(FilterComposites(rest, first))
    in primes,
    GetPrimes2 = List.Primes,
    Table.Primes = (max) => Table.FromColumns({List.Primes(List.Sequence(2, max))}, {"Number"})
in
    Table.Primes(100)

Then here is the code for transforming your table with the number strings in them.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Duplicate = Table.DuplicateColumn(Source, "Number String", "Number String - Copy"),
    Split = Table.ExpandListColumn(Table.TransformColumns(Duplicate, {{"Number String - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Number String - Copy"),
    ToNumber = Table.TransformColumnTypes(Split,{{"Number String - Copy", Int64.Type}}),
    IsEven = Table.AddColumn(ToNumber, "IsEven", each if Number.IsEven([#"Number String - Copy"]) then 1 else 0),
    IsOdd = Table.AddColumn(IsEven, "IsOdd", each if Number.IsOdd([#"Number String - Copy"]) then 1 else 0),
    Merge = Table.NestedJoin(IsOdd,{"Number String - Copy"},Primes,{"Number"},"Primes",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Primes", {"Number"}, {"Number"}),
    IsPrime = Table.AddColumn(Expand, "IsPrime", each if [Number] = null then 0 else 1),
    Group = Table.Group(IsPrime, {"Number String"}, {{"IsEven", each List.Sum([IsEven]), type number}, {"IsOdd", each List.Sum([IsOdd]), type number}, {"IsPrime", each List.Sum([IsPrime]), type number}})
in
    Group

Results look like this.

<table valign="top"><caption>LEGO HTML</caption><col width="54"><col width="2080"><col width="2044"><col width="2168"><col width="2043">
<tr><td></td><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">C</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">D</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">E</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">F</font></th></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">1</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">Number String</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">IsEven</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">IsOdd</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">IsPrime</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">2</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">1-2-3-4-5-6-7-8-9-10-11-12-13-14-15</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">7</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">8</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">6</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">3</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">3-6-7-11-173-14</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">2</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">4</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">3</font></td></tr></table>

 

[Rick Rothstein]

Hi, Luthius...you'll notice we both used the VBA MOD function which takes a left and right argument unlike the Excel formula version. But, it's there.

It is not a good idea to use the VBA Mod operator (not Mod function) in a UDF as it will fail with sufficiently large enough numbers. The largest number the Mod operator can handle is 2147483647 (a Long data type) but Excel cells can contain numbers much greater than this, so if such a number is tested with the Mod operator, an Error message will be generated if not trapped (a similar problem exists for negative numbers). You do not have to fall back on using the Evaluate function to invoke Excel's MOD function though...

Even: If Number Like "*[02468]" Then

Odd: If Number Like "*[13579]" Then