Find and Replace… Amazing Functionality You Did Not Know

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

A picture containing drawing  Description automatically generated

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.

A screenshot of a cell phone  Description automatically generated

1- Finding and Replacing Text

A screenshot of a cell phone  Description automatically generated

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

A screenshot of a cell phone  Description automatically generated

Click on Find All ► the dialog box expands ► Hit CTRL + A► Close the Dialog box ► Type “Cash” followed by ► CTRL+ ENTER

A screenshot of a social media post  Description automatically generated

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

A screenshot of a social media post  Description automatically generated

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.

A screenshot of a social media post  Description automatically generated

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:

 

Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

SUBSCRIBE To Our Emailing List And Receive 3 FREE Gifts...Now

Receive 3 Instant Free Gifts  When Subscribing to our newsletter here below
Two Books( Excel Pivot Tables - Dynamic Charts) and The Magical Shortcuts Quick Reference Card Showing 78  easy to remember Shortcuts.
SUBSCRIBE NOW
close-link