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.

1,014 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