Check if a cell contains text

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

Let's say you want to ensure that a column contains text and not numbers or you want to find all orders by a particular salesperson. There are several ways to check if a cell contains text but the case of the text doesn't matter to you.

What do you want to do?

Find cells that contain text

Filter a list for rows that contain specific text

Check if a cell has any text in it

Check if a cell matches specific text

Check if part of a cell matches specific text


Find cells that contain text

  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 Find.
  2. In the Find what box, enter the text or numbers that you want to search for or choose a recent search from the Find what drop-down box.

 Note   You can use wildcard characters in your search criteria.

  1. If you want to specify a format for your search, click Format and make your selections in the Find Format dialog box.
  2. Click Options to further define your search. For example, you can search for all of the cells that contain the same kind of data, such as formulas.

In the Within box, you can select Sheet or Workbook to search a worksheet or an entire workbook.

  1. Click Find All or Find Next.

Find All lists every occurrence of the item that you are searching for and allows you to make a cell active by selecting a specific occurrence. You can sort the results of a Find All search by clicking a header.

 Note   To cancel a search in progress, press ESC.

Tip    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.), you can use filters to display only the rows that contain the desired data. For example, in a list that includes international sales data, you can display just domestic sales.

Top of Page Top of Page


Filter a list for rows that contain specific text

  1. Click a cell in the list that you want to filter.
  2. On the Data menu, point to Filter, and then click AutoFilter.
  3. Click the arrow Field arrow in the column that contains the numbers, and click (Custom).
  4. In the box on the left, click equals, or does not equal, contains, or does not contain.
  5. In the box on the right, enter the text that you want.
  6. If you need to find text values that share some characters but not others, use a wildcard character.

ShowHow?

The following wildcard characters can be used as comparison criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) for filters, and when searching and replacing content.

Use To find
? (question mark) Any single character
For example, sm?th finds "smith" and "smyth"
* (asterisk) Any number of characters
For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~ A question mark, asterisk, or tilde
For example, fy91~? finds "fy91?"

Top of Page Top of Page


Check if a cell has any text in it

To do this task, use the ISTEXT function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
A
Data
Davolio
123
Formula Description
=ISTEXT(A2) Checks to see if cell A2 has text (TRUE)
=ISTEXT(A3) Checks to see if cell A3 has text (FALSE)

Function details

ISTEXT

Top of Page Top of Page


Check if a cell matches specific text

To do this task, use the IF function. The IF function returns results that you specify based on a condition.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
A
Data
Davolio
Formula Description
=IF(A2="Davolio","OK", "Not OK") Checks to see if A2 is Davolio (OK)
=IF(A2="Buchanan", TRUE, FALSE) Checks to see if A2 is Buchanan (FALSE)

Function details

IF

Top of Page Top of Page


Check if part of a cell matches specific text

To do this task, use the IF, SEARCH and ISNUMBER functions.

 Note   The SEARCH function is case-insensitive.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
A
Data
Davolio
BD123
Formula Description (Result)
=IF(ISNUMBER(SEARCH("v",A2)),"OK", "Not OK") Checks to see if A2 contain the letter v (OK)
=ISNUMBER(SEARCH("BD",A3)) Checks to see if A3 contains BD (TRUE)

Function details

IF

ISNUMBER

SEARCH

Top of Page Top of Page

 
 
Applies to:
Excel 2003