Purpose
Return value
Syntax
=ISTEXT(value)
- value - The value to check.
Using the ISTEXT function
The ISTEXT function returns TRUE when a cell contains a text value , and FALSE if the cell contains any other value, or is empty. You can use the ISTEXT function to check if a cell contains a text value, or a numeric value entered as text.
The ISTEXT function takes one argument , value , which can be a cell reference, a formula, or a hardcoded value. Typically, value is entered as a cell reference like A1. When value is text, the ISTEXT function will return TRUE. If value is any other value, ISTEXT will return FALSE.
Examples
The ISTEXT function returns TRUE if value is text:
=ISTEXT("apple") // returns TRUE
=ISTEXT(100) // returns FALSE
If cell A1 contains the number 100, ISTEXT returns FALSE:
=ISTEXT(A1) // returns FALSE
If a cell contains a formula, ISTEXT checks the result of the formula:
=ISTEXT(10 &" apples") // returns TRUE
=ISTEXT(2+2) // returns FALSE
=ISTEXT(A1&B1) // returns TRUE
Note: the ampersand (&) is the concatenation operator in Excel. When values are concatenated, the result is text.
Count text values
To count cells in a range that contain text, you can use the SUMPRODUCT function like this:
=SUMPRODUCT(--ISTEXT(range))
The double negative coerces the TRUE and FALSE results from ISTEXT into 1s and 0s and SUMPRODUCT sums the result.
Notes
- Dates and times are numbers , not text.
- The ISNONTEXT function tests for non-text values.
Purpose
Return value
Syntax
=N(value)
- value - The value to convert to a number.
Using the N function
Use the N function to convert value to a number. The N function takes one argument , value , which can be a cell reference, a formula result, or a hardcoded value. Values are converted as shown below. The logical values TRUE and FALSE are converted to 1 and 0, and text values are converted to zero. Numeric values and errors are unaffected.
| Input value | Return value |
|---|---|
| Any number | Same number |
| A recognized date | A date in Excel serial number format |
| TRUE | 1 |
| FALSE | 0 |
| Error (#VALUE, #N/A, #NUM!, etc.) | Same error code |
| Other values | 0 |
The N function is provided for compatibility with other spreadsheet programs. In most cases, using the N function is unnecessary, because Excel automatically converts values when needed. However, the N function is a simple way to convert TRUE and FALSE to their numeric equivalents, 1 and 0, as mentioned below.
Examples
The N function converts text values to zero:
=N("apple") // returns 0
Numeric values and errors are not affected:
=N(100) // returns 100
=N(5/0) // returns #DIV/0!
The N function converts TRUE to 1 and FALSE to zero:
=N(TRUE) // returns 1
=N(FALSE) // returns 0
=N(3>1) // returns 1
=N(3<1) // returns 0
There are several ways to perform this conversion in Excel, which is useful in more advanced formulas. For example, the formula below will return a count of cells in a range that contain more than 100 characters :
=SUMPRODUCT(N(LEN(range)>100))
This article explains other ways to convert TRUE and FALSE to 1 and 0.
Notes
- The N function removes text values. The T function removes numeric values.
