Table of Contents
Mind Blowing Functionality of the “Find and Replace” Command
This blog article maps to my video on the same topic.
You can Download the Exercise File and follow along by clicking on the link here Below
The Find and Replace commands share one single dialog box with 2 tabs. This dialog box can be accessed by clicking on “Find & Select” to the right side of the Home Tab orby using the Shortcuts CTRL + F and CTRL + H.
The “Options” button enables you to acquire more functionality and it expands the dialog box.
1. Finding and Replacing Text
In this example I want to Find every occurrence of “MasterCard” and replace it with “Cash”, without using the “Replace” command.
I hit CTRL + F ► Click on Options
Click on Find All ► the dialog box expands ► Hit CTRL + A► Close the Dialog box ► Type “Cash” followed by ► CTRL+ ENTER
All values have been replaced.
2. Match Entire Cell Content
In this example we will replace the “West” region with “North” but Excel not only selects “West” but also “Midwest”. To avoid that Check the box ► Match entire cell content
3.Replace Formatting by selecting a Sample
In this example we want to “Find” every occurrence of a specific Formatting, that we will select with an “eye dropper” and replace it with another sample formatting from the worksheet.
4- Partial replacement in a Function in the entire Sheet
Watch the Video
5- Fixing a #REF! error
Watch the Video
6- Find and Replace Cell References
Watch the Video
7- Find and Replace text in Comments and Notes
Watch the Video
8- Replace All Functions in the Sheet
Watch the Video
9- Find and Replace Named Ranges
Watch the Video
10- Cleaning Imported Data
Watch the Video
11- Cleaning Imported Data
Watch the Video
12- Replacing Text with Emoji
Watch the Video
13- Amazing Automation with VBA
Watch the Video
Copy the code here under and switch to the Visual Basic Editor (ALT + F11)
Create a Module (Insert menu ► Module)
Paste the code in the module and close the Visual Basic Editor to go back to Excel.
Note a file having a code must be saved in a .XLSM format.
The code has a Shortcut CTRL + SHIFT + R
Watch the Video to learn how to use the code
Here is the VBA Code
Sub ReplaceAllAtOnce()
Dim Rng As Range
Dim Destination As Range
Dim ReplaceRng As Range
Set Destination = Application.Selection
Set Destination = Application.InputBox(“Original Range “, “wwww.OfficeInstructor.com”, Destination.Address, Type:=8)
Set ReplaceRng = Application.InputBox(“Replace With Range :”, “Nabil Mourad”, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
Destination.Replace what:=Rng.Value, Replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole
Next
Application.ScreenUpdating = True
End Sub
Watch the YouTube Video by clicking on the link: