EXCEL FIND & REPLACE - lesson 27

Lesson 27 - Excel Find & Replace/Find & Replace Values in Excel

FIND & REPLACE

As with most Microsoft Office applications Excel has a Find dialog and a Replace dialog. These make it possible to quickly find a particular piece of text or a value in a Worksheet or Worksheets.

There are two rules that apply to both of these dialog boxes and these are:

If We Only Have A Single Cell Selected They Search Through The Entire Worksheet.
If We Have More Than One Cell Selected, They Will Search Only Through The Selected Range.

The Find dialog box and Replace dialog box are very closely related. They are more often than not used in conjunction with each other. Basically, if you opt for Edit you will be able to use the Find and/or the Replace. Our recommendation is to forget about the Find box unless you are searching for text or values that reside in cell Comments. We will use cell Comments as our first example, but first we feel it is important to point out that the Look in box within the Find dialog box has three options; Formulas, Values and Comments. The Formulas and Values options can give very misleading results, so I recommend not using them. We will however, show you an alternative later.

The comments option of the Find dialog box does not have a Replace option and works like this:

On A Clean Worksheet, Type The Simple Formula =B1+2 In Cell A1.
In A2, Type The Number 2.
In A3, Insert A Cell Comment And Type The Number 2 Inside It.
In A4, Type The Word Cat, Push The Space Bar And Then Type Cat.
In A5, Insert Another Cell Comment And Type The Number 2.
In A6, Type =2+B6
With Any Single Cell Selected, Either Go To Home>Find & Select>Find Or Push Ctrl + F

Type The Number 2 In The Find What Box. You Will Notice That There Is A Search Box That Contains The Two Options By Rows Or By Columns. Note - These Options Will Have Very Little Bearing On A Worksheet Unless Virtually Every Cell Is Filled With Data, Which Is Very Unlikely! Even Then, It Will Only Make The Difference Of A Second Or Two In The Way It Searches
Have Comments Selected In The Look In Box, Then Click Find Next And Excel Will Take You First To Cell A3.
Click Find Next Again, And You Will Be Taken To Cell A5. Note - If You Keep Clicking Find Next, You Will Toggle Between A3 And A5.
What You Need To Do From Here Should You Wish To Replace The Number 2 Within The Cell Comment(S) Is Click Cancel And Edit The Comment As We Discussed Previously.

Let's now use the Edit>Replace dialog to replace text or values in our cells.

Again, With Any Single Cell Selected, Go To Home>Fine& Slect>Replace Or Use Ctrl + H.
Type The Word Cat (In Lower Case) In The Find What Box, Then Type The Word Dog (Lower Case) In The Replace With Box.
Check The Find Entire Cells Only Box And Click Replace. You Should Get A Message Come Up Telling You That Microsoft Excel Cannot Find A Match. This Is Because We Do Not Have The Single Word Cat In A Cell On Its Own, Which Is What The Find Entire Cells Only Option Forces It To Look For.
Deselect (Or Uncheck) The Find Entire Cells Only Checkbox And Click The Match Case Checkbox. Now Click The Find Next Button And We Should Be Taken Straight To Cell A4, Which Contains The Two Words Cat And Cat. One With The Uppercase C And One With A Lowercase C.
Click The Replace Button And Our Word Cat (With The Lowercase C) Should Be Replaced With The Word Dog. The Word Cat (With An Uppercase C) Will Be Left Unchanged, Even If You Click The Replace Button Again.
Click Close

We should note here that clicking the Replace button will only ever replace the text or value in the current active cell. Clicking the Replace All button will replace all matching text or values on the entire Worksheet, unless we had more than one cell or a range of cells selected before we activated the Replace dialog. But this is more reminiscent of popular y8 games and not what is mentioned above.

Let's now assume we want to replace the number 2, but only in cell A1, where it is part of our formula.

Select Any Cell On Your Worksheet Other Than Cell A1.
Push F5 And Click Special On The Go To Dialog Box. This Will Display What Is Known As The Go To Special Dialog Box.
Check The Formulas Option And Leave All The Sub-Options Below This, As It Does Not Matter In This Case.
Click OK
Now Push Ctrl + H Or Go To Edit>Replace. Type 2 In The Find What Box, And The Number 3 In The Replace With Box. Ensure The Find Entire Cells Only Option Is Not Checked.
Click Replace All And Then Cancel.
If You Then Click Back In Cell A1 And Look In Your Formula Bar, You Should Now See The Formula = B1+3. If You Click In Cell A6 You Should See =3+B6 In Your Formula Bar.

The reason the Replace All only replaced the number 2 in our formulas was simply because we had more than one cell selected, which is telling Excel to only replace the number 2 in the selected cells only.

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Go back to:

Lesson 1 - Excel Fundamentals

Lesson 2 - Starting Excel and Excel Workbooks

Lesson 3 - Excel Toolbars and Task Panes

Lesson 4 - Excel Worksheets

Lesson 5 - Excel Cells and Navigating a Worksheet

Lesson 6 - Excel Cut/Copying and Pasting Data

Lesson 7 - Excel Copying with the Fill Handle

Lesson 8 - Excel Paste Special

Lesson 9 - Excel Insert Command

Lesson 10 - Excel's default options