How to return Line number and its position number and length of string searched From Text File

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
177
Hello

Actually i would like to return Line number and the position number of cursor with length of string searched From Text File

I tried the following code from
StackOverflow thread 23369908

Basically the above thread showed the position of string searched for and as on one line only

Where as i would like msgbox to show "String Searched" Amt
in line 3 Position 1 Lenght 3
in line 7 Position 1 Lenght 3
in line 11 Position 1 Lenght 3

Example "String Searched" 8600
in line 7 Position 5 Lenght 4

Example "String Searched" 0X
in line 6 Position 21 Lenght 2
in line 10 Position 24 Lenght 2

Example "String Searched" 09
in line 1 Position 9 Lenght 2
in line 4 Position 9 Lenght 2
in line 10 Position 33 Lenght 2

Structure of "C:\ABC\TextFile.txt"
Date 19-09-2020
Reference Number NZ26X201249Y75140
Amt 2000.00

Date 28-09-2020
Reference Number MD00X1485Y47366
Amt 8600.00

Date 10-10-2020
Reference Number YN27220X1562M9109
Amt 7650.00

VBA Code:
Option Explicit
Public myFile As String
Public textLine As String

Private Sub UserForm_Initialize()
myFile = "C:\ABC\TextFile.txt"

Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textLine
TextBox2.text = TextBox2.text & textLine & vbCrLf
Loop
Close #1
End Sub

Private Sub CommandButton1_Click()
Call REVSrchStr_Load
End Sub


Private Sub RevSrchStr_Load()

   Dim myFile As String
   Dim text As String
   Dim textLine As String
   Dim posIAV As Integer
   Dim Ctr As Integer
   Dim Ctr2 As Integer
   Dim data() As String

   myFile = "C:\ABC\TextFile.txt"
   Ctr = 0
   Ctr2 = 0

   Open myFile For Input As #1

   Do While Not EOF(1)
   Line Input #1, textLine
   text = text & textLine & vbCrLf

   ' Increment Ctr since you're on the line now
   Ctr = Ctr + 1

   ' Check the current line to see if it contains the string we're looking for
   ' If it does, set Ctr2 to be the current line number
   If textLine Like TextBox1.text Then
      Ctr2 = Ctr
   End If

   Loop

   Close #1

   posIAV = InStr(text, TextBox1.text)
   MsgBox (posIAV) & " in number Line : " & Ctr
End Sub
Your help will be appreicated
SamD
162
 
Last edited:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
L

Legacy 456155

Guest
You will need a reference to Microsoft Scripting Runtime. Returns multiple occurrences in the same line as well. The example uses the default compare method which is case sensitive.
Example file Search Text File.xlsm in this folder.

This example would perform a case insensitive search.
MySearch = GetSearchResults("C:\ABC\TextFile.txt", "m", vbTextCompare)

In Static Module:
VBA Code:
Public Type SearchResults
    LineNumber As Long
    CharPosition As Long
    StrLen As Long
End Type

Function GetSearchResults( _
  FileFullName As String, _
  FindThis As String, _
  Optional CompareMethod As VbCompareMethod = vbBinaryCompare) _
  As SearchResults()
  
    Dim fso As New FileSystemObject, s As String, pos As Long, l As Long, sr As SearchResults, ret() As SearchResults, i As Long
    
    With fso.OpenTextFile(FileFullName)
        Do Until .AtEndOfStream
            l = l + 1
            s = .ReadLine
            pos = 1
            Do
                pos = InStr(pos, s, FindThis, CompareMethod)
                If pos > 0 Then
                    sr.CharPosition = pos
                    sr.LineNumber = l
                    sr.StrLen = Len(FindThis)
                    ReDim Preserve ret(i)
                    ret(i) = sr
                    i = i + 1
                    pos = pos + 1
                End If
            Loop Until pos = 0
        Loop
    End With
    GetSearchResults = ret
End Function


Sub Example()
    Dim MySearch() As SearchResults, i As Long
    
    MySearch = GetSearchResults("C:\ABC\TextFile.txt", "0X")
    For i = 0 To UBound(MySearch)
        With MySearch(i)
            Debug.Print "in line " & .LineNumber, "Position " & .CharPosition, "Length " & .StrLen
        End With
    Next
End Sub
 

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
177
Fantastic Dataluver just perfect 🍻

Thanks for the Folder Link

Last but not the least Will it be possible to get the Main String displayed
for eg i searched "AT"
Main string to be displayed as DATE
so
in Line 1, Position 1, Length 4, in Main String DATE
in Line 5, Position 1, Length 4, in Main String DATE
in Line 5, Position 1, Length 4, in Main String DATE

I searched for "6"
in Line 2, Position 21, Length 1, in Main String NZ26X201249Y75140
in Line 6, Position 31, Length 1, in Main String MD00X1485Y47366
in Line 6, Position 32, Length 1, in Main String MD00X1485Y47366
in Line 7, Position 6, Length 1, in Main String 8600.00
in Line 10, Position 28, Length 1, in Main String YN27220X1562M9109
in Line 11, Position 6, Length 1, in Main String 7650.00

SamD
163
 
Last edited:

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
177
I tried the following
VBA Code:
Option Explicit

Public Type SearchResults
    LineNumber As Long
    CharPosition As Long
    StrLen As Long
    srchdStr As String
End Type

Function GetSearchResults( _
  FileFullName As String, _
  FindThis As String, _
  Optional CompareMethod As VbCompareMethod = vbBinaryCompare) _
  As SearchResults()

    Dim fso As New FileSystemObject, s As String, pos As Long, l As Long, sr As SearchResults, ret() As SearchResults, i As Long
  
    With fso.OpenTextFile(FileFullName)
        Do Until .AtEndOfStream
            l = l + 1
            s = .ReadLine
            pos = 1
            Do
                pos = InStr(pos, s, FindThis, CompareMethod)
                If pos > 0 Then
                    sr.CharPosition = pos
                    sr.LineNumber = l
                    sr.StrLen = Len(FindThis)
                    sr.srchDstr = Split(s, " ")(1)
                    ReDim Preserve ret(i)
                    ret(i) = sr
                    i = i + 1
                    pos = pos + 1
                End If
            Loop Until pos = 0
        Loop
    End With
    GetSearchResults = ret
End Function


Sub Example()
    Dim MySearch() As SearchResults, i As Long
  
    MySearch = GetSearchResults("C:\ABC\TextFile.txt", "6", vbTextCompare)
  
    For i = 0 To UBound(MySearch)
        With MySearch(i)
            Debug.Print "in line " & .LineNumber, "Position " & .CharPosition, "Length " & .StrLen & " In Main String " & .srchdStr
        End With
    Next
End Sub
unfortunately The following sr.srchDstr = Split(s, " ")(1) gave the following results
in Line 2, Position 21, Length 1, in Main String Number
in Line 6, Position 31, Length 1, in Main String Number
in Line 6, Position 32, Length 1, in Main String Number
in Line 7, Position 6, Length 1, in Main String 8600.00
in Line 10, Position 28, Length 1, in Main String Number
in Line 11, Position 6, Length 1, in Main String 7650.00

Wish the Split function could execute in the following manner Split(s, " ")(2) but Not possible only
SamD
164
 

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
177
Hello

I've worked it out for what i desired as per #4 of this thread

Thanks
SamD
165
 

Watch MrExcel Video

Forum statistics

Threads
1,114,513
Messages
5,548,493
Members
410,840
Latest member
Kar3ousse
Top