search and replace text files based on list in excel [solved]

[ian762]

I am looking for a way to search and replace text files based on a list in excel.

i.e. I want to search text fileA - for the text string in worksheet1, column A and replace it with the text string in worksheet1, column B.

So I would search an entire folder of text files for the text in cell A1 and replace with the text in cell B1, then the same for A2 and B2, etc.

[GeneralDisarray]

Open the attached file, on Sheet1 - put your A and B list in place.

Add the PATH to the files (to the folder they sit in) in cell E1.

Then you can press the button to run the bulk find-n-replace operation on all the file. Below is the code that I ran.

(note: if you try putting the code into you own workbook, you'll need to edit it for the ranges I've highlighted. Probably easier to use this file).

Please Login or Register  to view this content.

[bulina2k]

just another method (same result)

Please Login or Register  to view this content.

[ian762]

I did not mention, that the text files I am wanting to search through aren't actually ".txt" files. They are ".gdfx" files, they open in plain text, if you do right-click edit in notepad. I see in the examples you guys left, they are looking for ".txt" files. I tried to change that part of the code to be ".gdfx", but I still am not able to get it to work.

[bulina2k]

I added a code to rename all the files in the directory to txt and open them and then to rename them back. try this way:

Please Login or Register  to view this content.

[ian762]

I got it figured out. I wasn't searching for the whole string. I changed the search text to be the entire string and it is working now. Thanks for your help guys. This is saving me hours of work!

[bulina2k]

What search text?
Haven't you used any of our code?

[ian762]

the text i needed changed in the file was xxx.xxx.xx, but it was actually part of a larger string yyyy="xxx.xxx.xx", so when i was only looking for xxx.xxx.xx, nothing was working. when I searched for the whole string it worked great.

[GeneralDisarray]

glad you got it working... did my code help? :D

[ian762]

yes, GeneralDissarray, I was using your code. I had it working yesterday before I left work. Now, of course I can't seem to run it. I am not sure what the issue is right now. I attached some example files, maybe you could try and see what I am missing.

[ian762]

I uploaded incorrect files, please use this folder, if you have time to check it out. Thanks

[ian762]

I think I figured out my issue. Maybe you can tell me if I am right.

I finally got it to work when I copied and pasted into columns A and B using "paste special" and choosing text only values not format. Then I was able to run the code with the expected values. I assume when I did a direct copy and paste that I was overwriting some sort of formatting you had set up in column A and B to set range for the VB loop?

At any rate I have it working now. Thanks Again!