Find and Replace Text in a List

Any text string within a list can be found, highlighted and optionally replaced.

Text can also be searched for in column headings.

Find Text

To find text within a list or column heading:

1. Enter the string to be searched for in the Find field in the Find Replace group of the Home Tab.

2. To specify whether the string should be searched for in a list and/or a column headings, click the arrow to the right of button next to the Find field to access the following drop-down list:

   

3. Select the Search in Headings and/or Search in Data Values options as required. Both may be selected at once. If only one option is selected, if that option is then de-selected, the other option automatically becomes selected.

4. To start the search, click the button with the binoculars symbol next to the Find field.

   If searching within column headings, the first heading that contains a match is then highlighted.

   If searching within data values, the first row that contains a match is then highlighted and the cell that contains the match is selected.

5. Click Next or the button again the find the next match. Click Previous to select previous matches for the current search.

   If searching within both headings and data values, matches within column headings are highlighted first, followed by matches within the list.

   If no matches are found, a message is displayed to inform the user of this.

   The drop-down list in the field lists all the strings searched for in the current sessions.

6. Alternatively, click Find in the drop-down list accessed from the arrow to the right of button next to the Find field, or right-click on a list and select the Find and Replace option from the menu that is then displayed. The Find and Replace window is then displayed:

   

7. Enter the string that is to be searched for in the Find what field. If the string has already been searched for during this session, it can be selected from the drop down list accessible from this field.

8. By default, the search is conducted down the list, column by column.

   To change the search order, or to selected option to conduct a more refined search, click the + button. Additional fields are then displayed in the window:

If the search is to be conducted row by row instead of column by column, click the By Row option.

If the search is to be conducted from the bottom of the list upwards instead of from the top of the list downwards, click the Up option.

To refine the terms of the search, check the Match case, Match entire cell contents. Match prefix and/or Match suffix checkboxes as required.

To proceed with the search, click Find Next.

The cell that contains the first match is then selected.

Click Find Next again to find the next match.

If no matches are found, a message is displayed to inform the user of this.

Click Close to exit the window.

Replace Text

To find and replace text either click Replace in the drop-down list accessed from the arrow to the right of button next to the Find field, or if the Find and Replace window is already open, click on the Replace tab.

The Replace tab of the Find and Replace window is then displayed:

Specify the search options as described above.

In addition, enter the text that is to replace the found text in the Replace with field.

To proceed with the search and replace, click Find Next.

The first row that contains a match is then highlighted and the cell that contains the match is selected.

If this text is to be replaced, click Replace.

To replace all matching text, click Replace All.

Whether or not a replace is implemented, click Find Next to find the next match.

If no matches are found, a message is displayed to inform the user of this.

Click Close to exit the window.