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.
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]
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”
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.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE