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

#### Luthius

##### Active Member
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

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
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
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
I ran your original and it doesn't return true for 1 and does return true for 2.

#### Luthius

##### Active Member
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
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
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
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
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: