Cell Contains Number? Check in Excel & Sheets
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This guide explains how to check whether a cell contains a number, whether you are working in Microsoft Excel, Google Sheets, or another spreadsheet program. You will learn the core formulas, how to handle cells that mix text and numbers, and how to avoid common pitfalls like hidden spaces or formatted dates.
Formula
The most direct way to test if a cell contains a number is the ISNUMBER function. It returns TRUE if the value is numeric and FALSE otherwise. The syntax is simply:
=ISNUMBER(A1)
For a cell that may contain text, numbers, or blanks, this is the standard check. If you need to test whether a cell contains a number somewhere within a text string (e.g., “Order 42”), use a combination like:
=SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))>0
This works by extracting each character, converting it to a number (which fails for letters), and counting how many conversions succeed. If any character is a digit, the result is TRUE.
Worked Example
Using the default value from the calculator block, suppose cell A1 contains 123. The formula =ISNUMBER(A1) evaluates as follows:
- Excel checks the data type of A1. It is numeric (even though it looks like text, it is stored as a number).
- ISNUMBER returns
TRUE.
If A1 contained "123" (text with an apostrophe or entered as text), ISNUMBER would return FALSE. The calculator block above simplifies this by treating any non-negative value under 1 trillion as a number, which works for most practical entries.
Why the Data Type Matters
In spreadsheets, a cell can hold a number, text, a date, a time, a boolean (TRUE/FALSE), or an error. The visual appearance does not always match the underlying type. For instance:
- A cell showing
123might be text if it was imported from a CSV with leading zeros. - A cell showing
01/01/2025is actually a number (the serial date) formatted as a date. - A cell with
TRUEis a boolean, not a number, although it can be coerced to 1 in arithmetic.
ISNUMBER only returns TRUE for actual numeric entries, not for dates, times, or booleans (though dates and times are stored as numbers in Excel, so ISNUMBER returns TRUE for them as well).
How to Handle Text That Contains Numbers
If you need to detect whether a text string contains any digit (e.g., “Room 5”), the simple ISNUMBER test fails. Use the array formula mentioned above, or in Excel 365 use TEXTSPLIT or REGEX functions if available. In Google Sheets, you can use REGEXMATCH:
=REGEXMATCH(A1, "[0-9]+")
This returns TRUE if any digit appears. For Excel 365, the equivalent is =ISNUMBER(FIND("1",A1)) but that only checks for a specific digit; better to use =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0 as an array formula (enter with Ctrl+Shift+Enter in older versions).
Common Mistakes and Pitfalls
- Hidden spaces: A cell with a leading or trailing space is text, even if it contains digits. Use
=TRIM(A1)to clean it first. - Numbers stored as text: Often caused by importing data or using an apostrophe. Convert with
=VALUE(A1)or use the “Text to Columns” feature. - Formatted as date: A date like
01/15/2025is a number in Excel. ISNUMBER returns TRUE, but you might not want to treat it as a plain number. Use=CELL("format",A1)to check the format. - Blank cells: ISNUMBER(blank) returns FALSE. If you need to treat blanks as numbers, use
=OR(ISNUMBER(A1),A1="").
How to Choose the Right Formula
Decide what you actually need:
- Is the entire cell a number? Use
=ISNUMBER(A1). - Does the cell contain any digit? Use the REGEX or array formula.
- Is the cell a number or a date? Use
=AND(ISNUMBER(A1),NOT(ISDATE(A1)))(in Excel, useISNUMBERwith a format check). - Is the cell non-blank and numeric? Use
=AND(ISNUMBER(A1),A1<>””).
Frequently Asked Questions
Why does ISNUMBER return FALSE for a cell that looks like a number?
The most common cause is that the cell is formatted as text or contains invisible characters like a leading apostrophe or spaces. Select the cell, check the Number Format dropdown in the Home tab, and look for a green triangle warning. Use =TRIM(A1) and then convert to number with =VALUE(A1).
How do I count how many cells in a range contain numbers?
Use =COUNT(A1:A10) – it counts numeric cells only, ignoring text and blanks. If you need to count cells that contain numbers as part of text, you would need a more complex array formula or a helper column.
Can I use ISNUMBER with conditional formatting?
Yes. Select the range, go to Conditional Formatting > New Rule > Use a formula, and enter =ISNUMBER(A1) (adjust the cell reference to the top-left of the selection). This will highlight all cells that contain numbers.
Does ISNUMBER work in Google Sheets?
Yes, Google Sheets has the same function. It works identically, returning TRUE for numeric values. For text-with-digits, use REGEXMATCH as shown earlier.
How do I test if a cell contains a number but not a date?
In Excel, dates are stored as numbers. To exclude dates, check the cell format: =AND(ISNUMBER(A1),LEFT(CELL("format",A1),1)="G") – “G” means general format. In Google Sheets, use =AND(ISNUMBER(A1),NOT(ISDATE(A1))).
What about cells with errors like #VALUE!?
ISNUMBER returns FALSE for error values. If you need to treat errors as non-numbers, that is fine. To check for errors, use ISERROR separately.
Limits and Alternatives
The ISNUMBER function is simple and reliable for the common case of a cell containing a pure number. For more complex needs, consider:
- ISNUMBER with SUMPRODUCT for counting numeric cells in a range.
- VBA or Google Apps Script for custom logic that cannot be expressed in a single formula.
- Power Query in Excel for cleaning data during import, where you can detect and convert data types before loading.
Remember that spreadsheet functions are case-insensitive and do not require special permissions. Always test on a small sample before applying to a large dataset.
Related
Formula used
in v | Value to test | - | 123 | 0 out is_num | Is a number? | - | if(and(v>=0,v
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.