Table of Contents
- HOW TEXT IS HANDLED IN EXCEL?
- CONDITIONS WHEN WE NEED TO CHECK THE PRESENCE OF NUMBER OR TEXT IN A CELL
- CHECK IF A CELL CONTAINS TEXT OR NUMBER
- HOW TO CONNECT THE PRESENCE OF TEXT OR NUMBER FURTHER IN EXCEL?
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
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)
=IF(ISTEXT(CELL ADDRESS), ACTION IF THE CELL CONTAIN TEXT, ACTION IF THE CELL DOESN’T CONTAIN TEXT)
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.
If you look at the picture above, we can understand the function used and how to check whether the cell contains the text or number. Let us understand a few examples.
When cell contains WHAT the first column IF CELL CONTAINS NUMBER is FALSE because the given data is a text, whereas the second column IF CELL CONTAINS TEXT shows TRUE as the data is text.
Similarly the next example 12 shows the TRUE for column G , but false for column H .
Similarly, the columns show the correct result for the remaining examples.
HOW TO CONNECT THE PRESENCE OF TEXT OR NUMBER FURTHER IN EXCEL?
Let us try any condition where we decide the output on the status of the cell if it contains a text or number.
We can easily make use of our standard IF FUNCTION in EXCEL to decide the further action.
For Example, we’ll create a condition where, if the cell contains a number it’ll say it’s a number otherwise it’ll say it’s a text.
FOLLOW THE STEPS TO USE IF TO FURTHER USE THE PRESENCE OF TEXT OR NUMBER [ IF ONLY TEXT OR NUMBERS ARE PRESENT ]
- The standard notation of the formula will be =IF(ISNUMBER(CELL ADDRESS),”It’s a number”, “It’s a text”).
- Or, we can reverse the notation and check Text instead. The formula will become =IF(ISTEXT(CELL ADDRESS),”It’s a Text”, “It’s a number”)
What if we have a blend i.e. Some blanks or any other characters or any errors too. Let us create a nested IF function for the same.
FOLLOW THE STEPS TO ACT ON THE BASIS OF PRESENCE OF TEXT OR NUMBER IN A CELL
- The standard formula will be =IF( condition 1, value if true, IF( condition 2, value if true, value if false)).
- This formula will become =IF( ISNUMBER(CELL ADDRESS),” It’s a number”, IF(ISTEXT(CELL ADDRESS),”It’s a Text”,”It’s something else”)).
- This function will precisely search the presence of text or number in Excel.