# How to find the recurring period string of a recurring decimal using VBA



## Juggler_IN (Dec 13, 2022)

I want to find the recurring period string of a recurring decimal. 

That is, for example:
1/3 = 0.(3) = 0.333333... with a period "3" of length 1.
1/7 = 0.(142857) = 0.142857142857... with a period "142857" of length 6.
1/15 = 0.0(6) = 0.066666... with a period "6" of length 1.

So, if my input string is x=0.142857142857142857142, the code should output 142857.

I have attempted a VBA function with a reference Java code at Periods and the code is:


```
Function findSequence(x As String) As String

    Dim n As Long, i As Long, j As Long, k As Long

    n = Len(x)

    For i = 1 To n - 1

        For j = i To n - 1

            k = j Mod i: If k = 0 Then k = 1 Else k = k

            If Mid(x, k, 1) <> Mid(x, j, 1) Then
                i = j
            Else
            End If

        Next j

        findSequence = Mid(x, 1, i + 1) ' (The issue appears to be at this line.)
        GoTo x1

    Next i

    findSequence = "Impossible"
    GoTo x1

x1:
    Exit Function

End Function
```

But, this code is not giving the required output. Any suggestions?


----------



## StephenCrump (Dec 13, 2022)

Here's my take on this.  

I've converted text to numbers to speed up processing, and assumed that you will strip  leading zeros.

I have also allowed partial matches, e.g. for 1231231, or 12312312, the answer suggested is 123.  If you want to insist on full matches, e.g. 123123 or 123123123 or 123123123 etc, you can add a condition requiring M/L to be integer, where M is the string length, and L is the sub-string length being tested.   

AB1Possible2StringRecurring?3111412112512312312123612121127121231212123814285714285714285791428571428142857109919929991992111212312123121231212123Sheet1Cell FormulasRangeFormulaB3:B11B3=FindSequence(A3)


```
Function FindSequence(s As String) As String
    
    Dim N() As Long, M As Long, i As Long, L As Long, j As Long, k As Long
    Dim bSuccess As Boolean
    
    M = Len(s)
    ReDim N(1 To M)
    
    For i = 1 To M
        N(i) = Mid(s, i, 1)
    Next i
    
    For L = 1 To M - 1
        For j = 1 To Application.RoundUp(M / L, 0) - 1
            For k = 1 To Application.Min(L, M - j * L)
                If N(k) <> N(j * L + k) Then
                    L = j * L + k - 1
                    GoTo EndLoop
                End If
            Next k
        Next j
        bSuccess = True
        GoTo EndFunction
EndLoop:
    Next L
    
EndFunction:
    FindSequence = IIf(bSuccess, Left(s, L), "n/a")
    
End Function
```


----------



## jolivanes (Dec 13, 2022)

Is it three full stops (Char 46) or an ellipsis (Char 133)?


----------



## Juggler_IN (Dec 14, 2022)

jolivanes said:


> Is it three full stops (Char 46) or an ellipsis (Char 133)?


It is three dots . . . to show it is an ongoing number.


----------



## Juggler_IN (Dec 14, 2022)

@StephenCrump;  ... the code addresses one type of non-terminating decimal that has only a period in its expansion. 

Meets examples, like 1/3 (0.333333), 1/7 (0.142857) .... these expansions have a period only after the decimal point. 0. followed by (3) and (142857).

But, it does not address expansions that have a pre-period before a period. Example, the decimal of 1/15 = 0.066666... = 0.0(6) and the decimal of 3227/555 which outputs 5.8144144144... = 5.8(144) ... here 144 is repeating infinitely and is the period and 8 is the period.

And, the command ?FindSequence("814414414") outputs n/a but the desired output is 144.


----------



## StephenCrump (Dec 14, 2022)

Another loop should allow for these possibilities.  Try this:

AB1Possible2StringRecurring?314285714281428574991992999199251212312123121231212123612345454545457814414414414481666666900778778778778Sheet1Cell FormulasRangeFormulaB3:B9B3=FindSequence(A3)


```
Function FindSequence(s As String) As String
   
    Dim N() As Long, M As Long, i As Long, L As Long, j As Long, k As Long, p As Long
    Dim bSuccess As Boolean
       
    For p = 0 To Len(s) - 2
        M = Len(s) - p
        ReDim N(1 To M)
       
        For i = 1 To M
            N(i) = Mid(s, i + p, 1)
        Next i
       
        For L = 1 To M - 1
            For j = 1 To Application.RoundUp(M / L, 0) - 1
                For k = 1 To Application.Min(L, M - j * L)
                    If N(k) <> N(j * L + k) Then
                        L = j * L + k - 1
                        GoTo EndLoop
                    End If
                Next k
            Next j
            bSuccess = True
            GoTo EndFunction
EndLoop:
        Next L
    Next p
   
EndFunction:
    FindSequence = IIf(bSuccess, Mid(s, p + 1, L), "n/a")
   
End Function
```

I haven't allowed for decimal points, but there's no reason to test any integer component for recurrence.


----------



## Juggler_IN (Dec 14, 2022)

@StephenCrump; thanks this works  ... I will test it for other values.


----------



## Juggler_IN (Dec 15, 2022)

@StephenCrump; is it possible to incorporate the auto-rounding of decimal cases? 

What I mean is, for eg, 1/6 = 0.166666666666666666... which is reported by Excel as 0.166666666666667, and 1/7 = 142857142857142857142857... which is reported by Excel as 0.142857142857143.

In the case of 1/6 the last digit 6 is rounded up to 7 and with 1/7 the last digit 2 is rounded up to 3.


----------



## StephenCrump (Dec 17, 2022)

I changed back to comparing strings - a digit by digit comparison won't work if we allow rounding to flow through.  

I haven't tested properly, so give it a good workout and see if you can break it:

AB166666626666676359595959559459595959659512345123451234123456123451234512351234571428571428142857814285714291428579991992999199210166666611778778778778123334n/a1344445n/a14555556515199200n/a1619950012001995001Sheet1Cell FormulasRangeFormulaB1:B16B1=FindSequence(A1)


```
Function FindSequence(s As String) As String
  
    Dim L As Long, M As Long, p As Long
    Dim test1 As String, test2 As String, s1 As String
    Dim bSuccess As Boolean
    Const PREC = 15
      
    For p = 0 To Len(s) - 2
        s1 = Right(s, Len(s) - p)
        M = Len(s1)
        For L = 1 To M - 1
            test1 = Application.Rept(Left(s1, L), 1 + Int(M / L))
            test2 = ""
            If Mid(test1, M + 1, 1) > "4" Then test2 = Left(test1, Application.Max(0, M - PREC)) & Right(Left(test1, M), PREC) + 1
            If Left(test1, M) = s1 Or Left(test2, M) = s1 Then
                bSuccess = s1 <> 0
                GoTo EndFunction
            End If
        Next L
    Next p
  
EndFunction:
    FindSequence = IIf(bSuccess, Left(s1, L), "n/a")
      
End Function
```


----------



## Juggler_IN (Dec 17, 2022)

@StephenCrump; Thanks, I will test it and revert.


----------



## Juggler_IN (Dec 13, 2022)

I want to find the recurring period string of a recurring decimal. 

That is, for example:
1/3 = 0.(3) = 0.333333... with a period "3" of length 1.
1/7 = 0.(142857) = 0.142857142857... with a period "142857" of length 6.
1/15 = 0.0(6) = 0.066666... with a period "6" of length 1.

So, if my input string is x=0.142857142857142857142, the code should output 142857.

I have attempted a VBA function with a reference Java code at Periods and the code is:


```
Function findSequence(x As String) As String

    Dim n As Long, i As Long, j As Long, k As Long

    n = Len(x)

    For i = 1 To n - 1

        For j = i To n - 1

            k = j Mod i: If k = 0 Then k = 1 Else k = k

            If Mid(x, k, 1) <> Mid(x, j, 1) Then
                i = j
            Else
            End If

        Next j

        findSequence = Mid(x, 1, i + 1) ' (The issue appears to be at this line.)
        GoTo x1

    Next i

    findSequence = "Impossible"
    GoTo x1

x1:
    Exit Function

End Function
```

But, this code is not giving the required output. Any suggestions?


----------



## Gokhan Aycan (Dec 17, 2022)

Hey, from what I have read I don't think this is exactly what you want but maybe you will like it. I used the second approach from How do you calculate how many decimal places there are before the repeating digits, given a fraction that expands to a repeating decimal?. You can only use integer values, whether string or long type.






You need this simple class: cRational

```
Public numerator As Long, numMultiplier As Long
Public divisor As Long, divMultiplier As Long
Public remainder As Long
Public approximate As String
Public index As Long
```


```
Public Function FindSequence(ByVal numerator As Variant, ByVal divisor As Variant, Optional ByVal NoDecimals As Long = 99) As String
    
    On Error GoTo errHandler
    
    numerator = CLng(numerator)
    divisor = CLng(divisor)
    
    On Error GoTo -1
    
    Dim i As Long
    Dim approximate As String, numInteger As Long
    Dim ratio As cRational, coll As Collection
    
    Set coll = New Collection
    
    approximate = ""
    NoDecimals = NoDecimals + 1
        
    If numerator >= divisor Then
        numInteger = numerator \ divisor
        numerator = numerator - divisor * numInteger
    End If
    
    For i = 1 To NoDecimals
        Set ratio = New cRational
        
        ratio.index = i
        ratio.numerator = numerator
        ratio.divisor = divisor
        ratio.numMultiplier = 10
        
        ratio.remainder = ratio.numMultiplier * numerator Mod divisor
        ratio.divMultiplier = (ratio.numMultiplier * numerator - ratio.remainder) / divisor
        
        approximate = approximate & ratio.divMultiplier
        ratio.approximate = approximate
        
        On Error Resume Next
        
        coll.Add ratio, CStr(numerator)
        
        If Err Then
            FindSequence = coll(coll.Count).approximate
            Dim temp As String
            temp = "(" & Mid(FindSequence, coll(CStr(numerator)).index) & ")"
            FindSequence = numInteger & "." & Left(FindSequence, coll(CStr(numerator)).index - 1) & temp
            If Right(FindSequence, 3) = "(0)" Then FindSequence = "Not Repeating"
            Exit For
        End If
        
        On Error GoTo -1
        
        numerator = ratio.remainder
        
        Set ratio = Nothing
    Next i
    
    If i = NoDecimals + 1 Then FindSequence = "Couldn't find within " & NoDecimals - 1 & " decimal places."
    
    Exit Function
    
errHandler:
    
    FindSequence = "Parameter is not an integer."
    
End Function
```


----------



## jdellasala (Dec 17, 2022)

Juggler_IN said:


> I want to find the recurring period string of a recurring decimal.
> 
> That is, for example:
> 1/3 = 0.(3) = 0.333333... with a period "3" of length 1.
> ...


You do know that there are FUNCTIONS that can alleviate the issues you are having if you use them. Like *ROUND*, ROUNDDOWN, ROUNDUP, *CEILING*, *FLOOR*, *ABS*, *INT*, *MOD*.... ROUNDUP and ROUNDDOWN may not be available in Excel 2003, but I bet the others are!


----------



## Juggler_IN (Dec 19, 2022)

?Gokhan Aycan; Thanks for the function code. It helps.


----------

