Thread: help for replace all macro

hi all,

is there any way, any formula or macro to replace certain words in certain column?

example :

1. sheet1: in column C2:C1000 contain so many words and i need to replace/remove some of the word (usually you can do it by replace it manually.. ctrl+h then replace all, but if the words that you need to remove more than 50 words then it is painful)

2. sheet2: column A : contain words that i need to remove

so i need a way maybe macro that can read sheet2 column A and applied it as replace all in sheet1 column C

is that possible?

thanks guys

You need to more exact.... are you saying

1) REMOVE all the words (strings) found listed on sheet2 column A from the LONGER strings on sheet1 column C? The matches are substrings from longer strings.

This problematic in that if you removed "cat" it would change the word "catch" to "ch", too.

2) REMOVE all the whole strings found listed on sheet2 column A from EXACT MATCH cells on sheet1 column C? The matches are to whole cells, not partial matches or substrings.

3) REPLACE substrings, the replacement values would be on sheet2 column B>?

4) REPLACE whole strings, the replacement values would be on sheet2 column B?


It would really be simplest if you just posted a sample workbook showing examples of Sheet2 (list) and then Sheet1 BEFORE and AFTER. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

hi,

someone help me with this macro

Sub bulkRemove2()
Dim w As Range
With Sheets("Sheet2")
For Each w In .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
Sheets("Sheet1").Range("C:C").Replace what:=w.Value, replacement:=""
Next w
End With
End Sub

but it still have small weaknesses in that macro

if you have in
A1: R1
A2 :R1a
A3: R1b

as the word to search and replace...then it will only replace R1 and leave the "a" and "b" word

any advice how to tweak this macro to work so i will look for every column?

thanks

nb: it is solved just put the R1a first (longest word first then it is done)

This is the main answer:

Option Explicit

Sub ReplaceMany()
Dim MyWords As Range, Word As Range

Set MyWords = Sheets("Sheet2").Range("B:B").SpecialCells(xlConstants)

For Each Word In MyWords
    Sheets("Sheet1").Range("A:A").Replace What:=Word & "*", Replacement:="/", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
Next Word

End Sub

There is a problem in that in two of your sample strings you have two / marks and it is messing with those. Do you have more instances where there are additional / characters in the string? Are they all a consistent syntax?

For instance, the two so far are:

1EXTR/41011/R2C
1EXTR/41011/AB

We could put in code to fix this one instance by replacing 1EXTR/ with 1EXTR^^ before running the main macro, then changing it back. Are there more?