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

Luthius

Active Member
Joined
Apr 5, 2011
Messages
271
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

Code:
[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]
 
Last edited:

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
How about this?

Code:
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
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
735
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.

Code:
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
Had to make an update to the ISPRIME function.

Code:
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
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
735
I ran your original and it doesn't return true for 1 and does return true for 2.
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
271
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

Well-known Member
Joined
May 8, 2018
Messages
735
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

Active Member
Joined
Apr 5, 2011
Messages
271
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
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.

Code:
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.

Code:
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>
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
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
 
Last edited:

Forum statistics

Threads
1,082,500
Messages
5,365,935
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top