ISNUMBER & ISTEXT Data Type Inspection
Learn how to inspect cell data types in Excel using ISNUMBER, ISTEXT, and TYPE. Audit ghost numbers formatted as text, prevent SUM errors, and validate inputs.
When importing financial ledgers or bank statements into Excel, numbers frequently arrive formatted as text strings. A column that looks like numbers might show a total of zero when you run =SUM(D2:D100) because Excel silently ignores text values during summation.
To inspect what type of data is actually stored inside a cell, Excel provides three core information formulas: ISNUMBER, ISTEXT, and TYPE.
Quick Comparison: Data Inspection Formulas
| Formula | What It Tests | Return Values | Best Fit in Real Work |
|---|---|---|---|
ISNUMBER(value) |
Whether a cell contains a valid number (including dates and times) | TRUE or FALSE |
Catching text-formatted numbers before summing financial totals. |
ISTEXT(value) |
Whether a cell stores plain text strings | TRUE or FALSE |
Validating customer names and form text fields. |
TYPE(value) |
Identifies the internal data category | Numeric code (1, 2, 4, 16, 64) | Comprehensive auditing and branching logic based on data type. |
The TYPE Formula Code Dictionary
The TYPE formula returns an integer code corresponding to the value category:
- 1: Number (includes dates and times)
- 2: Text string
- 4: Logical value (
TRUEorFALSE) - 16: Error code (
#N/A,#VALUE!, etc.) - 64: Array
The Dangerous "Silent SUM" Trap
If cell A1 contains the text string "100", standard addition =A1 + 20 returns 120 because the plus operator coerces text to numbers automatically. However, =SUM(A1, 20) returns only 20 because SUM ignores text cells without throwing an error!
Use ISNUMBER to audit import columns and ensure all values are real numbers before calculating financial balances.
Hands-on Practice Playground
Inspect the data records in the audit table below to identify true numbers versus text entries. Follow each objective to complete the exercise.
In cell C2, check if the raw value in B2 is a valid number.
In cell C2, test whether raw value B2 is a true numeric value using the ISNUMBER formula.
In cell D2, check if the raw value in B2 is stored as text.
In cell D2, verify if raw value B2 is stored as text using the ISTEXT formula.
In cell E2, determine the numeric type code for the value in B2.
In cell E2, identify the numeric type code for cell B2 using the TYPE formula.
Share This Lesson
Spread the word with your peers and challenge your friends