HOW TO CHECK IF A CELL CONTAINS TEXT OR NUMBER ?

INTRODUCTION

Whenever we prepare any report in excel, we have two constituents in any report.

The Text portion and the Numerical portion.

But just storing the text and numbers doesn’t make the super reports. Many times we need to automate the process in the reports to minimize the effort and improve the accuracy.

Many functions are provided by the Excel which work on Text and give us the useful output as well. But few problems are still left on which we need to apply some tricks with the available tools.

In this articles , we are going to learn the solution to the problem of checking if the cell contains the text or number.

HOW TEXT IS HANDLED IN EXCEL?

TEXT is simply the group of characters and strings of characters which convey the information about the different data and numbers in Excel.

Every character is connected with a code [ANSI].

Text comprises of the individual entity known as character which is the smallest bit which would be found in Excel.

We can perform the operations on the strings[Text] or the characters.

Characters are not limited to A to Z or a to z but many symbols are also included in this which we would see in the later part of the article.

TEXT IS AN INACTIVE NUMBER TYPE[FORMAT] IN EXCEL. ANYTHING STORED AS TEXT [NUMBER OR DATE] WON’T RESPOND TO ANY STANDARD FORMULAS OR FUNCTIONS BUT SPECIALLY DESIGNED TEXT FUNCTIONS. [EXCEPTIONS DO OCCUR IN CASE OF NUMBERS]

If we need to make anything inactive, such as Date to be non responding to the calculation, we put it as a text. Similarly if we want to avoid any calculations for a number it needs to be put as a text.

CONDITIONS WHEN WE NEED TO CHECK THE PRESENCE OF NUMBER OR TEXT IN A CELL

There can be many situations when we need to check the presence of number or text.

This is needed especially when we want to treat both the numbers and the text separately but they are present in the same column or row.

For such situations , normally we need to separate the columns or rows for both the text and numbers but if we want to perform the action in the single column or row, we may use this.

CHECK IF A CELL CONTAINS TEXT OR NUMBER

CONCEPT:

We may have text or numbers in any cell.

What if we want to know whether the cell contains a number or the text.

Well , it is very easy because Excel provides us with a direct function for the same. Or even if there were no function, we would have done it by playing with the codes.

FUNCTIONS ARE ALSO MADE FROM THE BASIC ENTITIES OF ANY PROGRAMMING LANGUAGE. IF WE KNOW THE BASICS, WE CAN CREATE OUR OWN FUNCTION.

Let us take an example and check if the cell contains a number or the Text.

So we have direct functions ISNUMBER() and ISTEXT().

If ISNUMBER(CELL CONTAINING VALUE)=TRUE that means the cell contains the number.

If ISTEXT(CELL CONTAINING VALUE)=TRUE that means the cell contains the TEXT.

GENERALIZED FORMULA TO CHECK AND ACT ON THE BASIS OF CONTENT OF THE CELL

=IF(ISNUMBER(CELL ADDRESS), ACTION IF THE CELL CONTAIN NUMBER, ACTION IF THE CELL DOESN’T CONTAIN NUMBER)

OR

=IF(ISTEXT(CELL ADDRESS), ACTION IF THE CELL CONTAIN TEXT, ACTION IF THE CELL DOESN’T CONTAIN TEXT)

EXAMPLES:

Let us take an example.

Suppose a column is there with a mix of text and numbers.

Let us check first, whether the cell contains a number and in the next column let us check if the cell contains text.

STEPS TO USE ISNUMBER OR ISTEXT.

• Select the cell where we want to get the output.
• Put the formula as =ISNUMBER(CELL CONTAINING THE VALUE) to check if the value is a number.
• Similarly put the formula as =ISTEXT(CELL CONTAINING THE VALUE) to check if the value is a Text.
• The result will be displayed as TRUE or FALSE.