“Mathematics is not yet ready for such problems.” [Paul Erdös]

Abstract

The Collatz conjecture states for positive integers:

If the number is even, halve it
If the number is odd, triple it and add one

If you apply above rules repetitively, you will always reach 1.

Example

If you start with 5, you will get 5, 16, 8, 4, 2, 1 which results in the Collatz length of 6.

Appendix – sbCollatz Code

Please read my Disclaimer.

Option Explicit

Function sbCollatz(s As String) As Long
'Calculates the Collatz length of a positive integer =
'returns count of iterations until result is 1.
'Excel is not the best tool to implement this but here we are:
'Source (EN): http://www.sulprobil.de/sbcollatz_en/
'Source (DE): http://www.berndplumhoff.de/sbcollatz_de/
'(C) (P) by Bernd Plumhoff 17-Jul-2022 PB V0.2
Dim b As Boolean, c As Integer
Dim i As Long, j As Long, k As Long, m As Long, n As Long, p As Long
n = Len(s)
m = n + 20 'We assume 20 additional digits will suffice
ReDim t(1 To m) As Integer
For i = 1 To n
    t(m - n + i) = Mid(s, i, 1)
Next i
b = False
For j = 1 To m - 1
    If t(j) <> 0 Then Exit For
Next j
k = 1
If j = m And t(m) < 2 Then
    t(m) = 1
    b = True
End If
Do While Not b
    k = k + 1
    Select Case t(m)
    Case 0, 2, 4, 6, 8
        'Divide by 2
        c = 0
        For j = 1 To m
            p = 5 * (t(j) Mod 2)
            t(j) = t(j) \ 2 + c
            c = p
        Next j
    Case 1, 3, 5, 7, 9
        'Multiply by 3 and add 1
        c = 1
        For j = m To 1 Step -1
            p = 3 * t(j) + c
            t(j) = p Mod 10
            c = p \ 10
        Next j
        Debug.Assert c = 0 'If we fail here the number of additional digits was too small
    Case Else
        Debug.Assert False
    End Select
    For j = 1 To m - 1
        If t(j) <> 0 Then Exit For
    Next j
    If j = m And t(m) = 1 Then b = True
    'If you like you can print out t() here
Loop
sbCollatz = k
End Function

Download

Please read my Disclaimer.

sbCollatz.xlsm [20 KB Excel file, open and use at your own risk]