Detecting a certain number of consequential numbers in a cell (VBA)

Question

I need help coming up with a function that detects a certain number of consequential numbers in a cell or range of cells.

I have a bunch of data in the form of descriptions with a sequence of an 8 digit number hidden between text. I need to be able to detect this 8 digit number in a cell and extract it to another cell.

The reason I can't just extract all the numbers in a string is because there are also other numbers in the same cell, so I need to be able to detect an 8 digit number specifically.

Thanks in advance!

Have you tried anything yet? If yes, please edit your post and add what you did to it! — R3uK, Jul 17 '15 at 09:39
Is there any commonality in these strings, for example they all have the same first or last digit, since that would be a good starting point. — Luuklag, Jul 17 '15 at 09:44
possible duplicate of [How to extract groups of numbers from a string in vba](http://stackoverflow.com/questions/15369485/how-to-extract-groups-of-numbers-from-a-string-in-vba) — Luuklag, Jul 17 '15 at 09:48

score 0 · Answer 1 · answered Jul 17 '15 at 09:58

This will give you the first 8-digits that is contained in any String.

Sub Selvi()
    MsgBox GetConsequentialDigits(Cells(1, 1), 8)
End Sub

I made this into a function so that you can also use it directly into Excel like this :

=GetConsequentialDigits(A1,8)

And here is the function :

Public Function GetConsequentialDigits(ByVal InCellString As String, ByVal DigitsNumber As Integer) As String
Dim Cpt As Integer, _
    TpStr As String, _
    TpRez As String
Cpt = 0
TpRez = vbNullString

For i = 1 To Len(InCellString) - 1
    If Cpt < DigitsNumber Then
        TpStr = Mid(InCellString, i, 1)
        If IsNumeric(TpStr) Then
            Cpt = Cpt + 1
        Else
            Cpt = 0
        End If
    Else
        TpRez = Trim(Mid(InCellString, i - DigitsNumber, DigitsNumber))
        Exit For
    End If
Next i

GetConsequentialDigits = TpRez
End Function

Detecting a certain number of consequential numbers in a cell (VBA)

1 Answers1