How to use the Excel Functions ISNUMBER and ISTEXT

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

There are two related built in functions in Microsoft Excel that we are discussing today.

They are ISNUMBER and ISTEXT.

They are both simple in concept and we will look at a few examples of how to use them a bit later.

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Kasper Langmann, Co-founder of Spreadsheeto

What Is ISNUMBER and ISTEXT?

The ISNUMBER function is an information function. It checks whether a value is a number.

This could be a value in a cell or a value that is a result of another formula.

The result of ISNUMBER is either TRUE or FALSE. This can prove very useful especially when used within a larger formula.

The ISTEXT function is designed to check a value to tell whether it is a text value.

You can use it to check whether a value in a cell is a numeric value entered as text. Like ISNUMBER, this function also returns either TRUE of FALSE.

Syntax

Both ISNUMBER and ISTEXT have the same syntax and only a single, required argument.

The syntax of the ISNUMBER and ISTEXT functions

=ISNUMBER(value)

=ISTEXT(value)

 The value argument is the value you want to check.

This can be a literal value, a cell reference, or a formula (to check the result). A point to note is that any number enclosed in double quotes is treated as text.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Practical use of the ISNUMBER function

To get the idea behind ISNUMBER and what it does, let’s look at a few examples of data that we can check using the function.

The image below shows several different types of data that we will use the ISNUMBER function on.

values-table

Now let’s look at what ISNUMBER tells us about these values.

isnumber-examples

You can see the formula itself in column C. You can see how simple it is from a syntax perspective.

The results in column B show whether the values in column A are in fact a number value or not.

Note that integers, percentages, and dates result in a TRUE value. But a text string like “Apple” or “Lawyer” do not.

You may be wondering why ISNUMBER’ returns FALSE for row 5 where the value appears to be the number 74,234.

Kasper Langmann, Co-founder of Spreadsheeto

This is because this value is not, in fact, a number. The fact that the value is left aligned in the cell is a tell-tale sign that this is a number stored as text.

Let’s use the ISTEXT function on the same set of data.

istext-examples

Note the TRUE result on row 5. This confirms what we were talking about.

Further note that the #DIV/0! error on row 4 is neither text or number.

Practical use of ISNUMBER

All this illustrates how ISNUMBER and ISTEXT work and what we use them for. Let’s turn our attention to using them in a real-life context.

In this next section, we are going to combine these functions with the VLOOKUP and IF functions. It is not imperative that you know how to use either, but we will attempt to explain as we go on.

Imagine that we have a table containing students with test score results. Also imagine that each student has a student number.

student-table

We have created another table to allow us to look up a student by their student number and return their score.

Consider a public facing tool where we want to protect the anonymity of the students.

We have thus created a lookup table to allow grade lookup only by student number. If someone tries to look up by student name, our formula will return a blank.

isnumber-vlookup

The important part of this big formula is that we are using ISNUMBER to test the value of cell D10.

This formula will return a blank (“”) if the value in D10 is anything other than a number.

If ISNUMBER evaluates to TRUE, the formula will run the VLOOKUP. This will find the score that corresponds to the student number typed into cell D10.

successful-lookup

And if we try to lookup a score by typing in the student name, we draw a blank.

no-lookup

Now let’s consider a different approach.

Kasper Langmann, Co-founder of Spreadsheeto

We can setup our lookup formula to test the lookup value on whether it is a text value or not.

Faculty doesn’t have time to keep up with student numbers anyway.

istext-vlookup

In this case, we have replaced ISNUMBER with ISTEXT in the same formula so we have to type in a student name to get a score.

istext-lookup

And if we try to lookup by student number, we will get nothing.

unsuccessful-istext

If you want to get even fancier, you can always use the IF function in this formula.

This would allow you to lookup either the student number or the student name to get a score. You can add another VLOOKUP in place of where we currently have the formula setup to return a blank (“”).

double-vlookup

Here, we have taken our previous formula where we integrated the ISNUMBER function.

Then we have replaced the “” for the value_if_false argument of the IF function with a VLOOKUP.

The VLOOKUP uses a text string value as its lookup value (like student name).

So, if you type in the student’s name, you will be able to see the appropriate score.

double-vlookup-name

But if you would like, you can also look up the score by the student number in the same lookup table.

double-vlookup-number

Conclusion

ISNUMBER and ISTEXT are very simple in concept.

On the surface, it might be difficult to see the utility of these two functions. Through our examples, you can now see how useful these two functions can be.