Need help for a code in macro (to identify alpha numeric numbers)

Hi,

I am preparing a macro which identifies and copies 9 digit alpha numeric code (alpha till 4). The code am using is "\b\w{0,9}\d{4,9}\b"

But this code is copying alpha numeric numbers more than 9 too.....

I need to prepare a macro which would take only 9 digit numbers (including alphabets till 5)

My full macro code is

Sub ExtractNumbers()

Dim Filename As String
Dim Filepath As String
Dim Filespec As String
Dim Match As Variant
Dim RegExp As Object
Dim R As Long
Dim Rng As Range
Dim TextFile As Integer
Dim TextLine As String
Dim Wks As Worksheet

' Output worksheet and starting cell.
Set Wks = Worksheets("Sheet2")
Set Rng = Wks.Range("A1")

' Location and name of text file.
Filename = "Test.txt"
Filepath = "C:\Documents and Settings\rra172\Desktop"

Set RegExp = CreateObject("VBScript.RegExp")
RegExp.Global = True
RegExp.Pattern = "\b\w{0,9}\d{4,9}\b"

' Add backslash to the end of the file path if needed.
If Right(Filepath, 1) <> "\" Then
Filespec = Filepath & "\" & Filename
Else
Filespec = Filepath & Filename
End If

TextFile = FreeFile

' Parse the 9 digit numbers from the text file and output them to the worksheet.
Open Filespec For Input Access Read As #TextFile
Do While Not EOF(TextFile)
Line Input #TextFile, TextLine
If RegExp.test(TextLine) = True Then
For Each Match In RegExp.Execute(TextLine)
Rng.Offset(R, 0).Value = Match
R = R + 1
Next Match
End If
Loop
Close #TextFile

End Sub

I am attaching the txt file from which this macro should copy the numbers. thanks

1) Not recommend "Match" for variable name as it is a reserved word for VBA
2) If 9 digits numbers only, Pattern should be "\b\d{9}\b"
I would write like this

Sub ExtractNumbers()

    Dim Filename As String
    Dim myMatches As Object
    Dim RegExp As Object
    Dim i As Long
    Dim Rng As Range
    Dim txt As String
    
    ' Output starting cell.
    
    Set Rng = Sheets("Sheet2").Range("A1")
    
    ' Location and name of text file.
    Filename = CreateObject("WScript.Shell").SpecialFolders("desktop") & "\Test.txt"
    
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.Global = True
    RegExp.Pattern = "\b\d{9}\b"
    
    ' Read entire text at one time
    txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(Filename).ReadAll
    
    ' Parse the 9 digit numbers from the text file and output them to the worksheet.
    Set myMatches = RegExp.Execute(txt)
    For i = 0 To myMatches.Count - 1
        Rng.Offset(i).Value = myMatches(i)
    Next

End Sub

When you post the code, use the code tag
e.g

[code]
'your code here
[/code]

Hello Raju Radhakrishnan,

I added code tags to your post. This keeps your original formatting, making it easier to read, and easier to copy. Please do this in future posts. If you don't know how, see my signature line.