Data Validation & Auditing
Highlight & Audit Numbers Stored with Embedded Unit or Currency Symbols (ISTEXT)
Catch imported entries like "125 kg", "approx 90", or "EUR 400" that masquerade as numbers and silently drop out of SUM() using --AND(B2 <> "", ISTEXT(B2)).
Why typing "125 kg" inside a numeric cell silently deletes 125 from SUM(B2:B6)!
When warehouse staff type units or currency prefixes directly inside a numeric cell ("125 kg", "40 pcs", "EUR 310") instead of using a number format or separate UOM column, Excel and web spreadsheets store the cell as a TEXT string—and SUM(B2:B6) silently treats every text cell as 0 without raising an error!
Highlighting and counting contaminated cells with C2 = --AND(B2 <> "", ISTEXT(B2)) immediately surfaces every non-blank text entry inside a supposed numeric column while leaving intentionally blank pending rows ("") unflagged!
| Ledger Entry in B2:B6 | Included in SUM(B2:B6)? | Contaminated Flag --AND(B<>"", ISTEXT(B)) | Correct Fix |
|---|---|---|---|
| 250 and 180 (True Numbers) | Yes ($430 Subtotal) | 0 (Valid Number) | Use custom number format for units |
| "125 kg" and "EUR 95" (Embedded Units!) | NO (Silently Skipped!) | 1 (Flagged — 2 Rows!) | Strip text units -> numeric 125 & 95 |
| "" (Blank Pending Row B5) | Ignored | 0 (Blank Allowed) | Not false-flagged as text symbol |
Count Contaminated Text-With-Symbol Entries in a Numeric Column
Sums row-level audit flags C2:C6 = --AND(B2 <> "", ISTEXT(B2)) across the shipment weight column.
=SUM(C2:C6)Audit Banner: Warn Before Publishing Unreconciled SUM(B2:B6)
Blocks silent under-reporting if any cell in B2:B6 contains text units; returns clean SUM(B2:B6) once all entries are numeric.
=IF(SUM(C2:C6) > 0, "Audit Warning: Text Symbols Excluded from Sum", SUM(B2:B6))Try the example
Use the original values or change them to test your understanding.