Find text or numbers in a worksheet

  1. Select the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells that you want to search.

If you want to search the entire worksheet, click any cell.

  1. On the Edit menu, click one of the following:
    • To find text or numbers, click Find.
    • To find and replace text, click Replace.
  2. In the Find what box, type the text or numbers that you want to search for, or click the arrow in the Find what box, and then click a recent search in the list.

You can use wildcard characters such as an asterisk (*) or a question mark (?) in your search criteria:

  • Use the asterisk to find any string of characters. For example, s*d finds "sad" and "started".
  • Use the question mark to find any single character. For example, s?t finds "sat" and "set".

 Note   You can find asterisks, question marks, and tilde characters (~) in worksheet data by preceding them with a tilde character in the Find what box. For example, to find data that contains "?", you would type ~? as your search criteria.

  1. Click Options to further define your search, and then do any of the following:
    • In the Within box, select Sheet or Workbook to search a worksheet or an entire workbook.
    • In the Search box, click By Rows or By Columns.
    • In the Look in box, click Formulas, Values, or Comments.
    • To search for case-sensitive content, select the Match case check box.
    • To search for cells that contain just the characters that you typed in the Find what box, select the Match entire cell contents check box.
  2. If you want to search for text or numbers that also have specific formatting, click Format and make your selections in the Find Format dialog box.

Tip    If you want to find cells that just match a specific format, you can delete any criteria in the Find what box and then select a specific cell format as an example. Click the arrow next to Format, click Choose Format From Cell, and then click the cell that has the formatting that you want to search for.

  1. Do one of the following:
    • To find text or numbers, click Find All or Find Next.

Tip    When you click Find All, every occurrence of the criteria that you are searching for will be listed, and you can make a cell active by clicking a specific occurrence in the list. You can sort the results of a Find All search by clicking a column heading.

  • To replace text or numbers, type the replacement characters in the Replace with box (or leave this box blank to replace the characters with nothing), and then click Find or Find All.

 Note   If the Replace with box is not available, click the Replace tab.

  1. Tip    If needed, you can cancel a search in progress by pressing ESC.
  2. To replace the highlighted occurrence or all occurrences of the found characters, click Replace or Replace All.

Tips

  • Excel saves the options that you used to define a previous search. For example, if you search worksheet data and cannot find characters that you know to be there, it is possible that formatting from a previous search was not cleared. Click Options, click the arrow next to Format, and then click Clear Find Format.
  • Another way to find data in a list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) is to use filters to display only the rows that contain the data that you want. For example, in a list that includes both domestic and international sales data, you can display only domestic sales.
  • You can also use the SEARCH and FIND functions to find text or numbers on a worksheet.

SEARCH returns the number of the character at which a specific character or text string is first found, beginning with start_num. Use SEARCH to determine the location of a character or text string within another text string so that you can use the MID or REPLACE function to change the text.

FIND finds one text string (find_text) within another text string (within_text) and returns the number of the starting position of find_text, from the first character of within_text. Unlike SEARCH, FIND is case-sensitive and doesn't allow wildcard characters.

  • By default, the Find button is not displayed on the Standard toolbar. If you search for data often, it may be helpful to add this button to that toolbar. On the Tools menu, click Customize, and then click the Commands tab. In the Categories box, click Edit, and then click Find in the Commands box. Drag Find to the location that you want on the Standard toolbar or any other toolbar.
 
 
Applies to:
Excel 2003