Check if a cell contains text

Microsoft Office Excel 2003

previous page next page

Use the ISTEXT function to do this task.

Worksheet example

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

Show How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. 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

ShowCheck if cell contents matches specific text, and return a value

Use the IF function to do this task. The IF function returns results you specify.

Worksheet example

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

ShowHow?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. 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

ShowFilter a list for rows that contain specific text

  1. Click a cell in the list 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 you want.
  6. If you need to find text values that share some characters but not others, use a wildcard character.

    Show How?

    The following wildcard characters can be used as comparison criteria 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?"

ShowCheck if part of a cell matches specific text, and return a value

Use the IF, FIND, SEARCH and ISNUMBER functions to do this task.

Worksheet example

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

ShowHow?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. 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(FIND("v",A2)),"OK", "Not OK") Checks to see if A2 contain the letter v (OK)
=ISNUMBER(FIND("BD",A3)) Checks to see if A3 contains BD (TRUE)

The formula above uses the following arguments.

Formula to check for text

Formula to check for text

Callout 1 search_for: is what you want to check for

Callout 2 to_search: the cell that has the text you want to check

Note  The FIND function is case sensitive. Use the SEARCH function in place of the the FIND function if you want to ignore case.

Function details

IF

ISNUMBER

FIND

SEARCH

ShowFind cells that contain text

  1. Select the range of cells you want to search.

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

  2. On the Edit menu, click Find.
  3. In the Find what box, enter the text or numbers 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.

  4. If you want to specify a format for your search, click Format and make your selections in the Find Format dialog box.

  5. 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.

  6. Click Find All or Find Next.

    Find All lists every occurrence of the item 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.

ShowTip

To find data in a list, 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.

previous page start next page