OzGrid

Lesson 27 - Excel Find and Replace

< Back to Search results

 Category: [Excel]  Demo Available 

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
Lesson 11 - Excel's Undo and Redo
Lesson 12 - Excel's Format Painter
Lesson 13 - Excel's Dates and Times
Lesson 14 - Excel's Custom Formats
Lesson 15 - Excel Formulas
Lesson 16 - Excel Cell References
Lesson 17 - Excel: Avoid Typing
Lesson 18 - Excel Formulae Arguments & Syntax
Lesson 19 - Excel Autosum Formula
Lesson 20 - Excel Auto Calculate
Lesson 21 - Excel's Insert Function
Lesson 22 - Excel's Useful Functions
Lesson 23 - Excel's Named Ranges
Lesson 24 - Excel's Constants and the Paste Name Dialog
Lesson 25 - Excel's Calculations
Lesson 26 - Excel Comments Cell

See also:

Lesson - 28 - Clear Excel Cell Contents
Lesson 29 - Effective Excel Printing 1
Lesson 30 - Effective Excel Printing 2
Lesson 31 - Sorting in Excel
Lesson 32 - Hide/Show Row/Columns in Excel
Lesson 33 - Auto-Formats in Excel
Lesson 34 - Creating a Basic Excel Spreadsheet
Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet
Lesson 36 - Excel Worksheet Protection
Lesson 37 - Excel IF Formula Nesting
Lesson 38 - Excel Function Now/Today Formulas

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

 


Gallery



stars (0 Reviews)