How to find an exact number match in a text file?

[frankT68]

I've thousands of text files that I need to search to see if they contain a particular ID number. If the text file contains that ID number, I need to output certain data. The ID number can be between 1 and 6 digits long. The code I've works if the ID number is a six digit number. But if it's shorter, the problem is that the search returns information about all text files that contain a similar number.
Example: If the ID number is 41, I get hits for all files that contain, for example, ID 41 or ID 141 or ID 4123, etc.

I've collected the data about the ID numbers that I need to check if they're contained in text files in a list in a column on a sheet in an Excel file. The code I created goes through the list of ID numbers and checks if they're included in the text files.

            Dim StrFile As String
            Dim strFileName As String

            Dim strLine As String
            Dim f As Integer
            Dim lngLine As Long
            Dim blnFound As Boolean

          Dim sFolder As String
    
        ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
    
    If sFolder <> "" Then ' if a file was chosen
        ' *********************
        ' put your code in here
        ' *********************
    
    End If

'ID number to search for
strSearch = ThisWorkbook.Sheets("Test ID").Range("A" & x)

StrFile = Dir(sFolder & "\*.txt")

Do While Len(StrFile) > 0

strFileName = sFolder & "\" & StrFile


            f = FreeFile
            Open strFileName For Input As #f
            Do While Not EOF(f)
                lngLine = lngLine + 1
                Line Input #f, strLine
                If InStr(1, strLine, strSearch, vbBinaryCompare) > 0 Then
                   Sheets("Podatki").Range("B" & y) = StrFile
                   Sheets("Podatki").Range("A" & y) = strSearch
                  
                   Sheets("Podatki").Range("D" & y) = InStr(1, strLine, strSearch, vbBinaryCompare)
                  

    
                     blnFound = True
             Exit Do
         End If
     Loop
     Close #f

     If Not blnFound Then
      MsgBox "Search string not found", vbInformation  
         
     End If

I'd like to know how I can find a specific ID number that is shorter than 6 digits?

 

[I_know_nuffin]

Have you tried adding a SPACE to the start and end of your number ?

 

[frankT68]

Have you tried adding a SPACE to the start and end of your number ?

Yes, but I still had trouble finding it. For example, when I searched for ID " 41 " (41 with a space before it and a space after it), I got no hits because there seems to be a line break after every ID number and I don't know how to search for a number and a line break. When I search for the ID number using a space in front of it, I still get some wrong results (e.g. 411235, 423567, 411).
All text files are prepared by converting a pdf file to a text file, and the part of the file containing the ID number information looks like this (I marked the ID number in red):

 

[I_know_nuffin]

try

strSearch = Chr(32) & strSearch & Chr(10)

 

[frankT68]

strSearch = Chr(32) & strSearch & Chr(10)

I tried but it doesn't work, it doesn't find anything. If I remove

& Chr(10)

then I get also some wrong results (see below

When I search for the ID number using a space in front of it, I still get some wrong results (e.g. 411235, 413567, 411).

I have also tried

strSearch = Chr(32) & strSearch & Chr(10) & Chr(13)

but doesn't work either.

 

[Alex Blakenburg]

My guess is that since you are reading a line at time the LineFeed character has already been stripped off.

If you can rely on the value always being at the end of the line how about using InstrRev and look for the space eg

Sub FindEndValue()

    Dim rng As Range
    Dim strSearch  As String
    Dim valFnd As Long, strFnd As String
   
    strSearch = 41
   
    Set rng = Range("A1")           ' Test value only - yours will come from your text line read
   
    valFnd = InStrRev(rng, " ")
    strFnd = Right(rng, Len(rng) - valFnd)
   
    If strSearch = strFnd Then
        MsgBox "Found " & strSearch
    Else
        MsgBox "Not Found " & strSearch
    End If

End Sub

 

[I_know_nuffin]

you could also try to find what the last char is by using something like the below to show the char code

tmpstr = Right(strline, 10)
For x = 1 To 10
Debug.Print Mid(tmpstr, x, 1), Asc(Mid(tmpstr, x, 1))
Next x

 

[frankT68]

My guess is that since you are reading a line at time the LineFeed character has already been stripped off.

If you can rely on the value always being at the end of the line how about using InstrRev and look for the space eg

Sub FindEndValue()

    Dim rng As Range
    Dim strSearch  As String
    Dim valFnd As Long, strFnd As String
 
    strSearch = 41
 
    Set rng = Range("A1")           ' Test value only - yours will come from your text line read
 
    valFnd = InStrRev(rng, " ")
    strFnd = Right(rng, Len(rng) - valFnd)
 
    If strSearch = strFnd Then
        MsgBox "Found " & strSearch
    Else
        MsgBox "Not Found " & strSearch
    End If

End Sub

you could also try to find what the last char is by using something like the below to show the char code

tmpstr = Right(strline, 10)
For x = 1 To 10
Debug.Print Mid(tmpstr, x, 1), Asc(Mid(tmpstr, x, 1))
Next x

In the meantime, I've found a solution that works for me. Instead of reading one line at a time, I import the entire text file into Excel (one at a time), then remove all formatting (including line breaks) using code

CleanedText = Application.WorksheetFunction.Clean(TextToBeCleaned)

and check the "cleaned" text to see if it contains the ID numbers I'm looking for.

 

[Alex Blakenburg]

If you post the actual working code, you can mark your own post as the solution.

 

[frankT68]

If you post the actual working code, you can mark your own post as the solution.

I know that, but I still need to edit it a bit before it's suitable for posting (eg translate the notes because I didn't write them in English). Right now I'm tight on time, but next week will be better.

I've just noticed I somehow managed to delete the thank you in the previous post. So Alex and I_know_nuffin thanks for the tips.