When you import raw data into Microsoft Excel from a legacy CRM system or an old database, the formatting is often completely broken. What appears to be a column of numerical product IDs (e.g., 1045) might actually be formatted as plain text by the system. If you try to run mathematical formulas—like SUM or AVERAGE—on text-formatted numbers, Excel will immediately crash and output an error. Before you analyze imported data, you must use the ISTEXT and ISNUMBER functions to validate the data types.
Understanding the Validation Functions
Both ISTEXT and ISNUMBER are simple boolean functions. They evaluate a single cell and return either TRUE or FALSE depending on how Excel’s internal engine categorizes the data inside that cell.
- =ISTEXT(A2): If cell A2 contains letters, words, or numbers that are explicitly formatted as text, this formula returns TRUE. If the cell contains a pure, mathematically valid number, it returns FALSE.
- =ISNUMBER(A2): This performs the exact opposite check. If the cell contains a pure number (including dates, which Excel calculates as numbers), it returns TRUE. If it contains text, it returns FALSE.
How to Audit an Entire Column
If you have a column of 5,000 prices and you suspect a few of them were corrupted into text during an import, manually checking them is impossible. You can use these functions to instantly audit the entire column.
- Create a brand-new column directly next to your imported price data (e.g., Column B).
- In the first empty cell (B2), type the formula:
=ISNUMBER(A2) - Press Enter. It should return TRUE if the price is valid.
- Click on cell B2, grab the small green square in the bottom-right corner (the Fill Handle), and drag it all the way down to the bottom of your 5,000 rows.
Now, you have a helper column filled with TRUE and FALSE values. You can simply turn on Excel’s Data Filter tool (Ctrl+Shift+L), click the drop-down arrow on your helper column, and filter the list to only show the FALSE rows. This instantly isolates the corrupted, text-formatted prices, allowing you to fix them manually or run a conversion formula like =VALUE() on them before they break your final financial report.