Excel Find Replace in batches

  • Thread starter Thread starter jelderto
  • Start date Start date
J

jelderto

The standard Find & Replace tool in excel allows only a single item to
replace another single item. I am looking for help on how to "batch"
replace, i.e. replace all instances of January, February, March with
Jan, Feb, Mar. I need to do this against thousands and thousands of
entries. Does anyone have a way of referencing a "library" of master
replacements and applying them to an immense worksheet of text values
at once?
 
Here are a couple you can modify to suit

Sub ColorIt()'make a list.
For Each cel In [mylist]'list
For Each c In Range("a4:g40")'where to look
If Trim(c) = cel then
c.value=left(cel,3)

' c.Interior.ColorIndex = 46
'c.Font.ColorIndex = 2
End If
Next c
Next cel
End Sub
=====
This will probably be faster due to find vs for each

Sub Colorit2()
For Each cel In [mylist]
With Worksheets("yourworksheetname").Cells
Set c = .Find(cel, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.value=left(cel,3)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub
 
Thank you. This code will just replace the identified target with the
first three letters of the identified target? I apologize if my post
was unclear - I need to replace the entire text, i.e. Bechton-Dickinson
with "BD and Johnson & Johnson with "J&J", etc... Ideally the solution
would be a macro rather than VBA, but I am desperate and would try
anything!

THANK YOU SO MUCH!
 
Another way is to create a new worksheet.

Put your list of "change from" in A1:Axxx.
Put your list of "change to" in B1:Bxxx.

You could use a formula like:
=left(a1,3)
and drag down.

Then come back and fix the non-standard ones.

Then after you've cleaned that list up, you could run a macro like:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myList As Range
Dim myChangeWks As Worksheet

Set myChangeWks = Worksheets("sheet2")

With Worksheets("sheet1")
Set myList = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myList.Cells
If IsEmpty(myCell) _
Or IsEmpty(myCell.Offset(0, 1)) Then
'do nothing
Else
myChangeWks.Cells.Replace _
what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
MatchCase:=False
End If
Next myCell

End Sub



Adjust the sheet names to match your workbook (and maybe the xlwhole to
xlpart????).
 
Back
Top