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: