How to use ISTEXT Function in Excel

Sometimes, we have a lot of data rows in the Excel dataset and we encounter a situation where we need to know the type of the data that is contained in each cell. In some cases, we need to know whether the data in the cell is a Text or not. In that case we can use the ISTEXT function to identify this difference.

In today’s tutorial we’ll learn how to use Excel’s ISTEXT function to differentiate text data from other data types contained in the dataset. Let’s consider this simple example dataset. ISTEXT function will return TRUE when the data type is TEXT and will return a FALSE otherwise.

This seems like a very simple case and we can tell by looking at the data, which entries are text and which are numeric. However, when we’ll run the data through the formula, we’ll know the actual answer which will be very interesting to know. We’ll explain the reason for the results as well when we apply the formula.

Step 1 – Create the formula and apply to data

• The formula is very simple and it only accepts a single parameter i.e., the cell reference. So, let’s use the following formula for identification of data types.

=ISTEXT(A2) or =ISTEXT(A2:G2)

Explanation of Results:

The obtained results are a bit confusing because we got a TRUE for Age column which was a number i.e., 32. The reason for this is that this number is being stored in Text Format as shown in the above animation.

Use ISTEXT to count cells that contains TEXT data

This formula can be used to identify the type of data to differentiate the TEXT data from other data types. It can also be used to count the number of cells containing TEXT data in a specified range. Let’s see how we can do that by following these steps.

Step 1 – Create the formula to count cell containing TEXT

• To count the number of cells that contain TEXT data, we’ll use the ISTEXT function inside SUMPRODUCT function. However, we’ll need some special characters which are double minus signs –. Write the following formula in an appropriate cell.

=SUMPRODUCT(–ISTEXT(A2:G2))

This formula will give us the count of the cells that contain the TEXT data. The special characters — in the formula convert the TRUE and FALSE output from ISTEXT(A2:G2) to a series of 1 and 0s. SUMPRODUCT then sums all the 1s and gives us the count of cells containing any text.