Use Excel To Find And Replace Words In Txt Files

HowdeeDoodee

Well-known Member
Joined
Nov 15, 2004
Messages
598
Can Excel be used to open, find and replace multiple words in text files, and then close and save the text files?

In col A I have the directory and file name where the text files are located.
In col B I have the words or terms to find
In col C I have the words or terms to replace replace when the col B term is found.

I would like the macro to go down the sheet, open the txt files listed in col A.
After the file is open I would like the macro to replace all the terms listed in col B with the associated term on the same row in col C.

The macro must find and replace all of the col B terms with all of the col C terms in all of the txt files, not just the txt file on the same row as the file name.

Thank you in advance for your reply.

Excel Workbook
ABC
1File NameFindReplace With
2E:\Replace\green.txtpowerpowerless
3E:\Replace\red.txttrustdistrustful
4E:\Replace\purple.txtbe freebe confined
5E:\Replace\brown.txtnot to worryworry a lot
6E:\Replace\black.txtgive it awaytake it back
7E:\Replace\Now.txtNIV HTML Previous Left8E:\Replace\Today.txtNIV HTML Middle1">Previous Verse (NIV)>>>
9E:\Replace\Scct.txtNIV HTML Middle210E:\Replace\Trd.txtNIV HTML Right">Next Verse (NIV)
Sheet1
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Like this. *** Take care *** the text files are deleted and replaced. Keep backups.
Code:
'========================================================================================
'- OPEN TEXT FILES. FIND AND REPLACE TEXT USING A LIST
'========================================================================================
'- Uses a Regular Expression
'- File name column A. Text to find column B. Replacement column C.
'- ** TEXT FILES ARE DELETED AND REPLACED WITH NEW ONES
'- Brian Baulsom August 2010
'========================================================================================
Sub TEXT_REPLACE()
    Dim MyRegExp As Object
    Dim MyPattern As String
    Dim MyMatches As Variant    ' Reg Exp extract set
    Dim FileString As String
    Dim MyFile As String
    Dim NewString As String
    Dim OldText As String  ' lookup value
    Dim NewText As String ' result
    Dim MyRow As Long
    Dim LastRow As Long
    '------------------------------------------------------------------------------------
    Set MyRegExp = CreateObject("VbScript.RegExp")
    Application.Calculation = xlCalculationManual
    '------------------------------------------------------------------------------------
    '- LOOP WORKSHEET ROWS
    LastRow = Range("A65536").End(xlUp).Row
    For MyRow = 2 To LastRow
        Application.StatusBar = MyRow - 1 & " / " & LastRow - 1
        MyFile = Cells(MyRow, "A").Value
        OldText = Cells(MyRow, "B").Value
        NewText = Cells(MyRow, "C").Value
        '-------------------------------------------------------------------------------
        '- READ THE FILE INTO MEMORY AND CLOSE IT
        Open MyFile For Input As #1
            FileString = Input(FileLen(MyFile), #1)
        Close #1
        '-------------------------------------------------------------------------------
        '- EXECUTE REGULAR EXPRESSION REPLACE
        With MyRegExp
            .Global = True
            .pattern = OldText
            .ignorecase = True
            NewString = .Replace(FileString, NewText)
        End With
        '-------------------------------------------------------------------------------
        '- DELETE THE OLD FILE & REPLACE WITH NEW
        Kill MyFile
        Open MyFile For Append As #1
        Print #1, NewString
        Close #1
        '-------------------------------------------------------------------------------
        FileString = ""
        NewString = ""
    Next
    '-----------------------------------------------------------------------------------
    MsgBox ("Done")
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub
'========== EOP ========================================================================
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,519
Messages
5,837,823
Members
430,517
Latest member
chessypack86

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
Top