# How to use the ISNUMBER function

The ISNUMBER function checks if a value is a number, returns TRUE or FALSE.

#### Table of Contents

## 1. ISNUMBER Function Syntax

ISNUMBER(*value*)

## 2. ISNUMBER Function Arguments

value |
Required. The value you want to check if it is a number. |

## 3. ISNUMBER function example

The image above shows the ISNUMBER function in column C. The first value is text value "A" in cell B3, the ISNUMBER function returns FALSE meaning the value is not a number.

The second value in cell B4 contains an Excel date, it contains a number formatted as date. The ISNUMBER function returns TRUE.

The third value is number 5 and the ISNUMBER function returns TRUE, this also applies to negative numbers and 0 (zero).

The fourth value is a boolean value, the ISNUMBER function returns FALSE. Boolean values TRUE and FALSE are not numbers.

Formula in cell D3:

## 4. ISNUMBER function not working

Check your spelling, a #NAME? error is shown if a function name is unrecognized.

The image above shows the ISNUMBER function returning the boolean value FALSE in cell C4 despite the fact that cell B4 contains a number.

We can see that cell B4 contains an apostrophe before the actual number. This makes Excel identify the number as a text value, however, the apostrophe is not shown in the cell making it very hard to spot.

Excel defaults to right-aligned values for numbers and left-aligned values for text values, see the image above. This is not displayed if you use custom cell formatting.

## 5. Identify numbers stored as text

The image above demonstrates how to identify numbers stored as text in a column, the ISNUMBER function returns FALSE if a value is not a number.

Cell C8 contains boolean value FALSE, the corresponding value in cell B8 contains '-81 which makes Excel think this value is a text value. This may happen if you import data from external sources like databases or web pages.

Formula in cell C3:

It is not necessary to use a formula for each value, you can use a single array formula to process all values in cell range B3:B12. Read the next section to find out how.

### 5.1 Identify numbers stored as text in a cell range

The array formula in cell D3 checks if the values in cell range B3:B12 are all numbers. It looks like they all are numbers, however, the ISNUMBER function has identified at least one cell value containing a text value.

The formula returns TRUE if all values are numbers and FALSE if at least one value is not a number.

Array formula in cell D3:

#### 5.1.1 How to enter an array formula

Excel 365 users can skip the below steps, enter the formula as a regular formula.

- Doublepress with left mouse button on cell D3 with the left mouse button.
- Enter the above array formula.
- Press and hold CTRL + SHIFT simultaneously.
- Press ENTER once.
- Release all keys.

The array formula begins and ends with curly brackets, see the image above. They appear automatically, don't enter these characters yourself.

#### 5.1.2 Explaining formula in cell D3

**Step 1 - Check if the value is a number**

ISNUMBER(B3:B12)

becomes

ISNUMBER({-7; 97; 56; 64; -96; "-81"; 62; 99; 57; 85})

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; **FALSE**; TRUE; TRUE; TRUE; TRUE}.

**Step 2 - Check if all boolean values are TRUE**

The AND function returns TRUE if all values are TRUE.

AND(logical1, [logical2], ...)

AND(ISNUMBER(B3:B12))

becomes

AND({TRUE; TRUE; TRUE; TRUE; TRUE; **FALSE**; TRUE; TRUE; TRUE; TRUE})

and returns FALSE. All values are not TRUE.

## 6. Highlight numbers stored as text

The image above shows numbers in cell range B3:B12, one cell is highlighted light-grey so you can easily spot non-numbers. Here is how you can do the same:

- Select cell range B3:B12.
- Go to tab "Home" on the ribbon.
- Press with mouse on the "Conditional Formatting" button on the ribbon. A popup menu appears.
- Press with mouse on "New Rule...".

- A dialog box appears. Press with mouse on "Use a formula to determine which cells to format".

- Type the following formula:
=ISNUMBER(B3)=FALSE
- Press with left mouse button on "Format..." button. Another dialog box appears.

- Press with left mouse button on tab "Fill".
- Pick a color to highlight cells with.
- Press with left mouse button on OK button.
- Press with left mouse button on OK button.

### 6.1 Sort highlighted numbers stored as text

You can sort cells based on cell color to quickly find numbers stored as text in larger data sets. Here is how:

- Select the data range with the mouse.
- Press with right mouse button on on the selected cell range. A popup menu appears.

- Press with mouse on "Sort".
- Press with mouse on "Custom Sort...". A dialog box appears.
- Sort on "Cell Color".
- Pick the color below "Order", see the image below.
- Select "On Top".

- Press with left mouse button on OK.

## 7. Can the ISNUMBER function handle error values?

The ISNUMBER function is really useful sometimes because it returns FALSE also from error values, as well. Most Excel functions return an error if they are fed with an error value, however, not all of them.

Most Excel functions that begin with IS handle error values. The image above shows three different error values, #DIV/0, #N/A, and #VALUE! errors. The ISNUMBER function returns FALSE for those values.

## 8. Identify digits in a string

The image above shows a formula in cell C3 that returns TRUE if at least one character in the string is a digit.

Excel 365 formula in cell C3:

Alternative array formula:

### 8.1 Explaining formula in cell C3

#### Step 1 - Count characters

The LEN function returns the number of characters in a given string.

LEN(value)

LEN(B3)

becomes

LEN("Abb")

and returns 3.

#### Step 2 - Create a sequence of numbers from 1 to n

The SEQUENCE function creates a list of sequential numbers

SEQUENCE(*rows*, [*columns*], [*start*], [*step*])

SEQUENCE(LEN(B3))

becomes

SEQUENCE(3)

and returns {1; 2; 3}.

#### Step 3 - Split characters in string one by one

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

MID(*text*, *start_num*, *num_chars*)

MID(B3, 1, SEQUENCE(LEN(B3)))

becomes

MID(B3, 1, {1; 2; 3})

becomes

MID("Abb", 1, {1; 2; 3})

and returns {"A"; "b"; "b"}.

#### Step 4 - Multiply characters by 1

The asterisk character lets you multiply values in an Excel formula. This step is needed in order to convert digits to numbers. For example, "2" becomes 2.

ISNUMBER("2") returns FALSE and we don't want that.

MID(B3, 1, SEQUENCE(LEN(B3)))*1

becomes

{"A"; "b"; "b"}*1

and returns

{#VALUE!; #VALUE!; #VALUE!}. The error value appears when we try to multiply a letter with a number. It is simply not possible.

#### Step 5 - Check if charcater is a number

ISNUMBER(MID(B3, 1, SEQUENCE(LEN(B3)))*1)

becomes

ISNUMBER({#VALUE!; #VALUE!; #VALUE!})

and returns

{FALSE; FALSE; FALSE}.

#### Step 6 - Check if at least one character is a digit

The OR function returns TRUE if at least one of the values is TRUE.

OR(ISNUMBER(MID(B3, 1, SEQUENCE(LEN(B3)))*1))

becomes

OR({FALSE; FALSE; FALSE})

and returns FALSE. No digit in this string.

### 'ISNUMBER' function examples

The following 34 articles contain the ISNUMBER function.

This post explains how to lookup a value and return multiple values. No array formula required.

The array formula in cell F3 counts cells in column B that contains at least one of the values in […]

The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

I this article I will show you how to get numerical values from a cell range manually and using an […]

The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]

The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]

EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]

Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]

The picture above shows different values in column B and a formula in column C that tries to identifies the […]

This article demonstrates different formulas based on if a cell contains a given text. Formula in cell C3: =B3=$E$3 The […]

The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]

This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]

This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]

This article demonstrates formulas that match two conditions in a column each and return another value on the same row […]

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]

This article demonstrates a formula that searches a cell (partial match) based on values in a table and returns a […]

This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]

Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

This article demonstrates array formulas that perform a wildcard search based on a sequence of values. The formulas return the […]

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]

What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

## Functions in this article

### Functions in 'Information' category

The ISNUMBER function function is one of many functions in the 'Information' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form