Cleaning imported data
ISNUMBER and ISTEXT: spot numbers stored as text that SUM ignores
Diagnose why SUM under-reports imported amounts by checking each cell’s stored data type with ISNUMBER and ISTEXT before converting.
Why SUM(A2:A4) misses a cell that looks like 42
Cell A2 holds numeric 42, cell A3 holds text "42" (for example from a text function or CSV import, shown here via ="42"), and cell A4 holds "42 kg". Visually A2 and A3 both display 42, yet =SUM(A2:A4) returns 42 instead of 84 because SUM ignores text cells.
| Cell | Stored content | ISNUMBER | ISTEXT |
|---|---|---|---|
| A2 | 42 (number) | true | false |
| A3 | ="42" (text string) | false | true |
| A4 | "42 kg" (text with unit) | false | true |
Audit stored types with ISNUMBER and ISTEXT
=ISNUMBER(A2) returns TRUE while =ISNUMBER(A3) returns FALSE and =ISTEXT(A3) returns TRUE—confirming immediately why A3 was skipped by SUM.
=ISNUMBER(A3)Convert clean numeric text with VALUE
=VALUE(A3) converts "42" into true numeric 42 (ISNUMBER becomes TRUE), whereas =VALUE(A4) on "42 kg" returns #VALUE! because of the trailing unit letters.
=ISNUMBER(VALUE(A3))Try the example
Use the original values or change them to test your understanding.