Find if the number is Prime or show the prime factors using excel formula?

Question

I have of integers in Col A and in col B i want to show result 'Prime' if it doesn't have further factors for the number itself. This goes like this if the number for example is 37 result will be 'Prime' and if its 44 then result will be 2x2x11. How can i do this using excel formula? Screen shot :

score 4 · Accepted Answer · answered Mar 29 '17 at 02:50

Disclaimer: code below is ported from this very useful VB.NET example

Option Explicit

Sub Test()
    Debug.Print FindFactors(2)
    Debug.Print FindFactors(3)
    Debug.Print FindFactors(11)
    Debug.Print FindFactors(12)
    Debug.Print FindFactors(13)
    Debug.Print FindFactors(16)
    Debug.Print FindFactors(17)
    Debug.Print FindFactors(24)
    Debug.Print FindFactors(25)
    Debug.Print FindFactors(11234)
    Debug.Print FindFactors(67894)
End Sub

Function FindFactors(lngNumber As Long) As String
    Dim collFactors As Collection
    Dim lngFactor As Long
    Dim lngCounter As Long
    Dim strFactors As String
    Dim strFactor As String

    Set collFactors = New Collection

    ' Take out the 2s.
    Do While (lngNumber Mod 2 = 0)
        collFactors.Add 2
        lngNumber = lngNumber / 2
    Loop

    ' Take out other primes.
    lngFactor = 3
    Do While (lngFactor * lngFactor <= lngNumber)
        If (lngNumber Mod lngFactor = 0) Then
            ' This is a factor.
            collFactors.Add lngFactor
            lngNumber = lngNumber / lngFactor
        Else
            ' Go to the next odd number.
            lngFactor = lngFactor + 2
        End If
    Loop

    ' If num is not 1, then whatever is left is prime.
    If lngNumber > 1 Then
        collFactors.Add lngNumber
    End If

    ' make a string out of collection
    strFactors = ""
    If collFactors.Count = 1 Then
        strFactors = "Prime"
    Else
        For lngCounter = 1 To collFactors.Count
            strFactors = strFactors & collFactors(lngCounter)
            If lngCounter < collFactors.Count Then
                strFactors = strFactors & "x"
            End If
        Next lngCounter

    End If

    FindFactors = strFactors

End Function

Gives an output of:

Prime
Prime
Prime
2x2x3
Prime
2x2x2x2
Prime
2x2x2x3
5x5
2x41x137
2x83x409

Can be used in a worksheet:

John Coleman · Answer 2 · 2017-03-29T03:08:07.657

Here is a somewhat straightforward recursive version. It is based on the idea that once you identify a factor you divide the number by that factor and then turn your attention to factoring the rest.

Function Factor(ByVal n As Long, Optional FirstTrial As Long = 2) As String
    Dim i As Long
    Dim t As Long
    Dim limit As Long
    Dim rest As String
    Dim s As String

    If n = 1 Then
        Factor = n
        Exit Function
    End If
    limit = Int(Sqr(n))
    t = FirstTrial
    Do While t <= limit
        If n Mod t = 0 Then
            rest = Factor(n / t, t)
            If rest <> "1" Then
                s = t & "x" & rest
            End If
            Factor = s
            Exit Function
        Else
            If t = 2 Then t = 3 Else t = t + 2
        End If
    Loop
    'if we get here:
    Factor = n
End Function

Function PrimeOrFactor(n As Long) As String
    Dim s As String
    s = Factor(n)
    If n = 1 Then
        PrimeOrFactor = "Neither"
    ElseIf (s) = Trim(n) Then
        PrimeOrFactor = "Prime"
    Else
        PrimeOrFactor = s
    End If
End Function

Tested like:

Sub test()
    Dim i As Long
    For i = 1 To 20
        Cells(i, 1) = i
        Cells(i, 2) = PrimeOrFactor(i)
    Next i
End Sub

Output:

score 1 · Answer 3 · answered Feb 18 '22 at 15:57

Using LET and dynamic arrays allows for the following without VBA.

=LET(x, SEQUENCE(A1),
     factors, FILTER(x, MOD(A1,x) = 0),
     factorMatrix, 1 * (MOD(factors, TRANSPOSE(factors)) = 0),
     primeFactors, FILTER(factors, MMULT(factorMatrix, factors ^ 0) = 2),
     primeFactorList, IF(MOD(A1, primeFactors ^ SEQUENCE(1, 20)) = 0, primeFactors, ""),
     factorProduct, TEXTJOIN("x",, primeFactorList),
 IF(A1 = 1, "Neither", IF(factorProduct=A1&"","Prime",factorProduct)))

It works for numbers up to 2^20.

score 0 · Answer 4 · answered Jun 05 '19 at 00:31

A slight modification to the excellent code of John Coleman above, using Mod with Doubles included below, will allow factoring integers up to Excel's 15 digit limit. Numbers with large factors may be noticeably slower. For example, 562,951,983,465,953 factored correctly as 16,777,259 x 33,554,467 in about 5 seconds on a Core i3.

Function Factor(ByVal n As Double, Optional FirstTrial As Double = 2) As String 'Changed
    Dim i As Long
    Dim t As Double 'Changed
    Dim limit As Long
    Dim rest As String
    Dim s As String

    If n = 1 Then
        Factor = n
        Exit Function
    End If
    limit = Int(Sqr(n))
    t = FirstTrial
    Do While t <= limit
        If FMod(t, n) = 0 Then 'Changed
    .
    .
    .


    Public Function FMod(a As Double, b As Double) As Double
        FMod = a - Fix(a / b) * b

        'http://en.wikipedia.org/wiki/Machine_epsilon
        'Unfortunately, this function can only be accurate when `a / b` is outside [-2.22E-16,+2.22E-16]
        'Without this correction, FMod(.66, .06) = 5.55111512312578E-17 when it should be 0
        If FMod >= -2 ^ -52 And FMod <= 2 ^ -52 Then '+/- 2.22E-16
            FMod = 0
        End If
    End Function

Find if the number is Prime or show the prime factors using excel formula?

4 Answers4