Find & Replace for large list in Excel

Question

I would like to find and replace a large list of words in excel; If I have the set of words I'd like to find/search through in Sheet1, ColumnA and then if Sheet2, ColumnA reflects what is to be found and ColumnB contains the word(s) to replace found word with (all comma separated values), how do I go about doing this so that the replacements end up back in Sheet1 ColumnA?

I suspect this requires a macro, which I am not very familiar with.

Many thanks in advance for your time and assistance!

I did find some threads on this site that seem to point towards what I'm needing but I am still struggling to wrap my head around the code for my specific use. — user3850410
I am keywording photographs, so all keywords pertaining to one filename are contained within a cell, separated by commas — user3850410
It would be a lot easier on you if they were all in separate cells. You can use the text to columns feature to put them in separate cells for you. After that, one of the solutions you've found should be adaptable for you — tigeravatar

Myles Myles · Accepted Answer · 2014-07-17T20:05:55

It's not super efficient but it will get the job done. You will have to account for the length of your two lists as well as if you change your sheet names.

Public Sub findsometext()

For i = 1 To 10 
' change 10 to however many items are in your replacement list 
' start at 2 if your data has headers
Worksheets("Sheet2").Activate
target = Cells(i, 1)
replacer = Cells(i, 2)
Worksheets("Sheet1").Activate


    For j = 1 To 10 
' change 10 to however many items are in your data list to be processed  
' start at 2 if your data has headers
        Cells(j, 1) = Replace(Cells(j, 1), target, replacer)
    Next j

Next i


End Sub

Find & Replace for large list in Excel

1 Answers