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’ll learn the way to find out the count of the cells containing a specific text.
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.
COUNT CELLS WITH SPECIFIC TEXT
Let us find out this trick to count the cells with specific text.
In this task we need the following functions.
1. A function which can Count the specific items. COUNTIF FUNCTION would work for this.
For simple counting, this function would suffice.
Let us try with an example.
Suppose we have the following data with us.
THE DATA FOR THE EXAMPLE LIES IN THE CELLS E17:E25
Let us find out the number of words with the text “ate” and “ly” and “lo”
STEPS TO COUNT THE NUMBER OF WORDS WITH SPECIFIC TEXT:
- Select the cell where we want to find out the count with the text “ATE” AT THE END.
- Enter the formula as =COUNT(ARRAY WITH ALL THE CELLS,”*ATE”). For our example the exact formula will be =COUNTIF(E18:E25,”*ATE”)
- Select the cell where we want to find out the count with the text “LY” AT THE END.
- Enter the formula as =COUNT(ARRAY WITH ALL THE CELLS,”*LY”). For our example the exact formula will be =COUNTIF(E18:E25,”*LY”)
- Select the cell where we want to find out the count with the text “EA” AT ANY POSITION.
- Enter the formula as =COUNT(ARRAY WITH ALL THE CELLS,”*EA*”). For our example the exact formula will be =COUNTIF(E18:E25,”*EA*”)
In this example we have made use of the * character which is one of the WILDCARD CHARACTERS in Excel.
The first argument of the COUNTIF function is the array containing all the words. i.e. E18:E25
For the first example, we want to find the number of words which has ATE as the suffix [ at the end of the word] . We used the second argument as *ATE which means ANY WORD CONTAINING ANY CHARACTERS OR SEQUENCES BUT ENDING AT ATE.
It’ll search out all the words ending with ATE and count them and return the result.
For the second example, we wanted to find out the number of words which are ending with LY. We used the second argument as *LY which means ANY WORD CONTAINING ANY CHARACTERS OR SEQUENCES BUT ENDING AT LY.
It’ll search out all the words ending with LY and count them and return the result.
In the third example, we want to search the number of words containing the text pattern EA anywhere in the word.
EA can be at the starting, in the middle or at the end of any word.
For this, we used the second argument as *EA*.
The * at both the end means to accommodate all the words containing any letters or characters before EA or after EA.
It’ll search for all the words containing the pattern EA anywhere in the given word.