06 Finding and Replacing Text or Data

Difficulty Level: Beginner

Excel Version: XP

Assumed Knowledge: None

 

 

The aim of the tutorial is to:

 

show you how to quickly search for certain text including words, numbers and symbols and to replace them efficiently.

 

Finding text or data

You may want to search a worksheet for a particular word or number, or a string of words and/or numbers. The Find and Replace box is useful for this purpose.

To open the Find and Replace box:

  1. Click on Edit on the menu bar.
  2. In the pop-down menu click on Find.

or

  1. Hold down the CTRL key.
  2. Quickly press the F key (CTRL + F).
  3. Release the CTRL key.

The Find and Replace box will appear in the screen:

To find a word, number or string of words and/or numbers:

  1. Open the Find and Replace box (as above).
  2. In the window next to Find what: type in the word and/or number that you want to find.
  3. Click on Find Next. The Find and Replace box will remain open.
    When the word or number has been found the relevant part of the worksheet will be shown on the screen and a box will appear around the cell containing the word or number. The contents of this cell will also appear in the formula bar above the worksheet. Click the mouse in the formula bar, amend the text if necessary and then press the Enter key to confirm the amendments.
  4. Repeats steps 2 and 3 to find other words or numbers.
  5. When you have finished click on Close to close the Find and Replace box.

To find all occurrences of a word, number or string of words and/or numbers:

  1. Open the Find and Replace box (as above).
  2. In the window next to Find what: type in the word and/or number that you want to find.
  3. Click on Find All. The Find and Replace box will remain open and will show the location of each occurrence of the word or number. The illustration below shows the book number, worksheet number and cell reference for each time the word cyberzone appears in a worksheet:

    To jump to a particular occurrence of the word or number click on the relevant row in the above box. You can then make any necessary changes in the way described above.
  4. When you have finished click on Close in the Find and Replace box to close it.

 

Replacing text or data

You may want to find a word or number and replace it with a different word or number. The quickest way to do this is to use the Replace tab in the Find and Replace box. This is particularly useful if you are working on a lengthy worksheet that includes the same word or number several times. For example a worksheet may have to contain numbers in thousands numerous times but instead of typing 000 for thousands each time it would be quicker to type in the number with K instead of 000 (i.e. 10K instead of 10000 or 23K instead of 23000) throughout the worksheet, then go back and find each occurrence of K and replace it with 000.

To open the Find and Replace box to replace a word or number:

  1. Click on Edit on the menu bar.
  2. In the pop-down menu click on Replace.

or

  1. Hold down the CTRL key.
  2. Quickly press the H key (CTRL + H).
  3. Release the CTRL key.

To replace a word or number:

  1. Open the Find and Replace box (as above).
  2. In the window next to Find what: type in the word or number that you want to find and replace.
  3. In the window next to Replace with: type in the word or number that you want to replace the existing word or number with.
  4. Click Find Next. The Find and Replace box will remain open.
    When the word or number has been found the relevant part of the worksheet will be shown on the screen and a box will appear around the cell containing the word or number. If you want to replace the word or number click on Replace.
  5. Click on Find Next to skip any found words or numbers that should not be replaced.
  6. When you have finished click on Close in the Find and Replace box to close it.

To replace all occurrences of a word, number or string of words and/or numbers:

  1. Open the Find and Replace box (as above).
  2. In the window next to Find what: type in the word and/or number that you want to find.
  3. In the window next to Replace with: type in the replacement word or number.
  4. Click on Replace All.
  5. When you have finished click on Close in the Find and Replace box to close it.

To replace some but not all occurrences of a word, number or string of words and/or numbers:

  1. Open the Find and Replace box (as above).
  2. In the window next to Find what: type in the word and/or number that you want to find.
  3. In the window next to Replace with: type in the replacement word or number.
  4. Click on Find All. The Find and Replace box will remain open and will show the location of each occurrence of the word or number. The illustration below shows the book number, worksheet number and cell reference for each time the number 15K appears in a worksheet:

  5. Click on Find Next to search for each occurrence sequentially.
  6. To jump to a particular occurrence of the word or number to replace it click on the relevant row in the above box and then click on Replace.
  7. When you have finished click on Close in the Find and Replace box to close it.

 

Exercises

For exercises to practice the above click here.

 

Back to Excel Home Page