Search words in Text file

yinkajewole

Active Member
Joined
Nov 23, 2018
Messages
281
Assuming I want to search "He resides at Alaska"
How can i find this in a text file even when it is scattered all over in the file.
So that if each of these words are found in the text file, it should give me a message box "Yes"
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
One way

Code:
Sub SimpleSearch()
    Dim myFSO As FileSystemObject
    Dim path As String, fileName As String
    Dim TSO As TextStream
    Dim Txt As String
    Dim SA, VA
    Dim I As Integer, J As Integer, Cnt As Integer

    path = "C:\Users\User1\Documents\"                'your path
    fileName = "tmp1.txt"                             'your file

    VA = Array("He", "resides", "at", "Alaska")

    Set myFSO = New FileSystemObject
    Set TSO = myFSO.OpenTextFile(path + fileName)
    Txt = TSO.ReadAll
    Txt = Replace(Txt, vbNewLine, " ")
    Txt = Replace(Txt, ".", " ")
    Txt = Replace(Txt, ",", " ")
    SA = Split(Txt, " ")

    Cnt = 0
    For I = LBound(VA) To UBound(VA)
        For J = LBound(SA) To UBound(SA)
            If SA(J) = VA(I) Then
                Cnt = Cnt + 1
                Exit For
            End If
        Next J
    Next I

    If Cnt = 4 Then
        MsgBox "Yes"
    End If
    TSO.Close
End Sub
 
Upvote 0
Another way. Change data in blue by your information.

Code:
Sub Macro4()
  Dim wPath As String, wFile As String, wString As String, f As Range, word As Variant, notE As Boolean
[COLOR=#0000ff]  [/COLOR]wPath[COLOR=#0000ff] = "C:\trabajo\"[/COLOR]
[COLOR=#0000ff]  [/COLOR]wFile[COLOR=#0000ff] = "test1.txt"[/COLOR]
[COLOR=#0000ff]  [/COLOR]wString[COLOR=#0000ff] = "He resides at Alaska"[/COLOR]
  
  Application.ScreenUpdating = False
  Workbooks.OpenText Filename:=wPath & wFile, Origin:=xlMSDOS
  For Each word In Split(wString, " ")
    Set f = Cells.Find(word, , xlValues, xlPart)
    If f Is Nothing Then notE = True
  Next
  ActiveWorkbook.Close
  If notE = False Then MsgBox "Yes" Else MsgBox "No exists"
End Sub
 
Upvote 0
I am assuming that you want to find each of those words as standalone words and not embedded within larger words. For example, if one of the words you want to find is "other" and that word is not in the text file, but the word "brotherhood" is in the text file... I am assuming you do not want the embedded word (shown in red) to be matched, correct? I also assume you do not want letter casing to matter. For example, if the word was at the beginning of a sentence, its first letter would be capitalized where as if it were an internal word to a sentence, its first letter would not be capitalized... I am assuming you would want the searched for word to match in either case, correct. I am also assuming that the word in the text file might not always be located next to a space, period or comma (it could be located next to a quote mark, dash, parentheses, or any other non-letter character), correct. If the answer to all of these questions is yes, then each of the routines posted so far are will not do what you want. Here is a macro that will...
Code:
Sub AreAllWordsInFile()
  Dim FileNum As Long, Num As Long, TotalFile As String, Word As Variant
  Dim wPath As String, wFile As String, wString As String
  wPath = "[B][COLOR="#FF0000"]c:\temp\[/COLOR][/B]"
  wFile = "[B][COLOR="#FF0000"]test.txt[/COLOR][/B]"
  wString = "He resides Alaska"
  FileNum = FreeFile
  Open wPath & wFile For Binary As #FileNum 
    TotalFile = Space(LOF(FileNum))
    Get #FileNum , , TotalFile
  Close #FileNum
  TotalFile = UCase(TotalFile)
  Num = 1
  For Each Word In Split(wString)
    Num = Num * (" " & TotalFile & " " Like "*[!0-9A-Za-z]" & UCase(Word) & "[!0-9A-Za-z]*")
  Next
  MsgBox Mid("No Yes", 1 + 3 * Abs(Num), 3)
End Sub
NOTE: Change the text in red to match your actual files path and name.
 
Last edited:
Upvote 0
@rlv01 Your code did not work
@
DanteAmor Yours working perfectly as I wanted. I just did not like the idea of opening and closing of workbooks.
@Rick Rothstein the words must not necessarily be a complete words, it can be a part of the words
Above all, I have now altered my code to suit my need. Thank you all for getting some few ideas from your codes.
 
Last edited:
Upvote 0
[/COLOR]@Rick Rothstein the words must not necessarily be a complete words, it can be a part of the words
Above all, I have now altered my code to suit my need. Thank you all for getting some few ideas from your codes.
I am not sure what code you modified, but if you are interested, the only change that needs to be made to the code I posted is to replace the single line of code inside the For.,.Next loop with this one...

Num = Num * (InStr(1, TotalFile, Word, vbTextCompare) > 0)
 
Upvote 0
@rlv01 Your code did not work
@
DanteAmor Yours working perfectly as I wanted. I just did not like the idea of opening and closing of workbooks.
@Rick Rothstein the words must not necessarily be a complete words, it can be a part of the words
Above all, I have now altered my code to suit my need. Thank you all for getting some few ideas from your codes.

I'm glad to help you. Thanks for the feedback.
 
Upvote 0
FWIW, The code I posted requires the the Microsoft Scripting Runtime library (Tools->Reference, Check "Microsoft Scripting Runtime")
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top