Search and replace certain strings in multiple text files

[swaggerbox]

I have multiple text files (*.txt) located in "E:\Test". I need to replace the text strings that contain the following terms with their corresponding replacements (see below)

"EXAMPLE_AA" replace with "AA\EXAMPLE_AA"
"EXAMPLE_CC" replace with "CC\EXAMPLE_CC"
"EXAMPLE_EE" replace with "EE\EXAMPLE_EE"
"EXAMPLE_GG" replace with "GG\EXAMPLE_GG"
"EXAMPLE_HH" replace with "HH\EXAMPLE_HH"
"EXAMPLE_JJ" replace with "JJ\EXAMPLE_JJ"
"EXAMPLE_KK" replace with "KK\EXAMPLE_KK"
"EXAMPLE_RR" replace with "RR\EXAMPLE_RR"
"following materials in EXAMPLE_" replace with "following materials in UU\EXAMPLE_"

There may be multiple instances of these terms in a single text file. I have only limited VBA experience so I would appreciate any help that I can get to get this started.

I need to do this in Excel since I'm not very familiar with MS Word.

[mohanvijay]

Use "TextStream" object From "Microsoft Scripting Runtime" library

[Teeroy]

There are 2 basic ways you can use a TextStream to do this. How large are the text files (approx. no. of lines)?

[Teeroy]

No answer? OK the code below will work on files of any length.

Even though this doesn't overwrite the input file I recommend always testing on copies of data first.

[vba]Sub ModifySpecificText()
Dim FSo As Object, objInputFolder As Object, objFile As Object
Dim inputFile As Object, outputFile As Object
Dim reg As Object, File_Location As String, contents As String

'set base file location
File_Location = "C:\Test\" '*********change to your location**********
'create objects
Set FSo = CreateObject("Scripting.FileSystemObject")
Set reg = CreateObject("VBScript.regexp")
'check for output location and create if necessary
If Not FSo.FolderExists(File_Location & "output") Then
FSo.CreateFolder (File_Location & "output") '*** output goes in folder under input directory ***
End If
'set folder object
Set objInputFolder = FSo.GetFolder(File_Location)
'Loop through file list and check for text files for input
For Each objFile In objInputFolder.Files
If FSo.GetExtensionName(objFile) = "txt" Then
Set inputFile = FSo.OpenTextFile(objFile)
Set outputFile = FSo.createtextfile(objInputFolder & "\output\" & FSo.GetFileName(objFile))
Do Until inputFile.AtEndOfStream
'run the regular expression replace to change the pattern
With reg
.Global = True
.IgnoreCase = False
.Pattern = "(EXAMPLE_)(\w\w)"
contents = .Replace(inputFile.readline, "$2" & "\" & "$1$2") & vbCrLf
End With
'write output
outputFile.write contents
Loop
End If
Next
End Sub[/vba]

ps: I couldn't decide whether you were using "EXAMPLE_" literally or as a placeholder for various text. I've coded it as a literal since more information would be needed to identify and match a placeholder.