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 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.
CHECK IF CELL CONTAINS TEXT OR NUMBER
This is again a part of the conditional assessment.
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.
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.