This was a query raised by one of a forum members at another site, where he had a worksheet filled with random numbers, and I have already answered that query at my post here. You may go through that post and read the logic behind the code.

vbacoder_Code_20190715.jpg

225 Views
'---------------------------------------------------------------------------------------
' Module    : bas_FindCellNo
' Type      : Module
' Author    : vsmathur
' Date      : 15-Jul-2019
' Purpose   :
'---------------------------------------------------------------------------------------

Option Explicit
Private varTmp As Variant

Function FindCellNo(ByVal strString As String) As String
'---------------------------------------------------------------------------------------
' Procedure : FindCellNo
' Author    : vsmathur
' Date      : 15-Jul-2019
' Purpose   : The Purpose of this Procedure is to <Purpose Here>
'---------------------------------------------------------------------------------------
'

     Dim str5Digits As String, str10Digits As String
     Dim r As Long
     On Error GoTo FindCellNo_Error
     
     str5Digits = CStr(FindFirst5Numerics(strString))
     For r = 1 To Len(strString)
          If Mid(strString, r, 5) = varTmp Then
               str10Digits = Mid(strString, r, 10)
               Exit For
          End If
     Next r
     FindCellNo = str10Digits

FindCellNo_Exit:

   On Error GoTo 0
   Exit Function

FindCellNo_Error:

     MsgBox "Error " & Err.Number & " on Line # " & Erl & " (" & Err.Description & ") in procedure FindCellNo of Module bas_FindCellNo"
     GoTo FindCellNo_Exit

     
End Function

Function FindFirst5Numerics(ByVal strString As String) As Long
'---------------------------------------------------------------------------------------
' Procedure : FindFirst5Numerics
' Author    : vsmathur
' Date      : 15-Jul-2019
' Purpose   : The Purpose of this Procedure is to <Purpose Here>
'---------------------------------------------------------------------------------------
'
     Dim i As Long
     Dim l As Long
   On Error GoTo FindFirst5Numerics_Error

     l = Len(strString) - 3
     For i = 3 To l
         varTmp = Mid(strString, i, 5)
          If IsNumeric(varTmp) Then
               FindFirst5Numerics = CLng(varTmp)
               Exit For
          End If
     Next i

FindFirst5Numerics_Exit:

   On Error GoTo 0
   Exit Function

FindFirst5Numerics_Error:

     MsgBox "Error " & Err.Number & " on Line # " & Erl & " (" & Err.Description & ") in procedure FindFirst5Numerics of Module bas_FindCellNo"
     GoTo FindFirst5Numerics_Exit

End Function