PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

HOW TO USE COUNTIF WITH TEXT IN EXCEL

SIMPLE TRICKS TO MANIPULATE TEXT IN EXCEL

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.

THIS WAS AN EXCERPT FROM THE FIRST ARTICLE OF THIS SERIES MANIPULATING TEXT IN EXCEL – PART I

 

IN THIS ARTICLE WE WOULD LEARN ABOUT THE USAGE OF COUNTIF FUNCTION WITH TEXT TO GET SOME EXCITING RESULTS.

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.
 
 

COUNTIF WITH TEXT VALUES IN EXCEL

COUNTIF FUNCTION counts the values with certain given conditions for example, if any number is smaller than a particular value, or larger than a fixed value or equal to any text etc.

In this section we would focus upon the text portion.

Let us find out with the help of examples, how we can apply COUNTIF to extract useful information from the data.

EXAMPLE 1: COUNTING THE NUMBER OF REPETITIONS OF A PARTICULAR TEXT IN EXCEL

WASHINGTON

PARIS

DELHI

DELHI

WASHINGTON

MOSCOW

ROME

ROME

MOSCOW

STEPS TO FIND THE NUMBER OF REPETITIONS OF A PARTICULAR TEXT

Select the cell where you want the result.

Put the function as =COUNTIF(RANGE,”TEXT”)

For our example, the formula will be =COUNTIF(F3:F11,G3) for the first example where we are trying to find out the number of times WASHINGTON is repeated. [ The first argument is the array reference which covers all the data in which we have to find out the repetitions. The second argument is the condition which we want to find. In our case we have put the reference of the text we want to find.

We can also put “WASHINGTON” directly.

 

THE COMPARISON OF TEXT VALUE IN THE COUNTIF FUNCTION IGNORES THE CASE AND TREATS UPPER AND LOWER CASE AS EQUALS.

COUNT THE NUMBER OF REPETITIONS USING COUNTIF
COUNT NUMBER OF REPETITIONS USING COUNTIF

EXAMPLE 2: COUNTING THE RESEMBLING WORDS IN EXCEL

Suppose we have the following group of words and we need to find out the resembling words.

A word can resemble in many ways.

Many words can have same prefixes, same suffixes, any pattern

Let us try different combinations.

GENERAL FORMULA TO COUNT THE WORDS HAVING SAME PREFIX:

=COUNTIF(RANGE,”PREFIX*”)  PREFIX is the letters of the prefix.

GENERAL FORMULA TO COUNT THE WORDS HAVING SAME SUFFIX:

=COUNTIF(RANGE,”* suffix”)  suffix are the letters of the suffix.

GENERAL FORMULA TO COUNT THE WORDS HAVING SAME PATTERN:

=COUNTIF(RANGE,”*pattern*”)  pattern is the text pattern.

GENERAL FORMULA TO FIND OUT THE WORD WHICH IS MISSING ONE LETTER.

=COUNTIF(RANGE,”lett?rs*”)  The letter at ? will be taken in to consideration and all possibilities will be counted.

Let us try these formulas in the examles.

DATA GIVEN:

KIT
HIT
FIT
LITE
LIGHT
KITE
MITE
MITTEN
KITTEN
SICK
WICK
LET

STEPS TO COUNT THE WORDS WITH SAME PREFIX.

  • Select the cell where you want the result.
  • Put the function as =COUNTIF(F16:F27,”LI*”)
  • Click OK.
  • The answer will appear as 2.

STEPS TO COUNT THE WORDS WITH SAME SUFFIX.

  • Select the cell where you want the result.
  • Put the function as =COUNTIF(F16:F27,”*EN”)
  • Click OK.
  • The answer will appear as 2.

STEPS TO COUNT THE WORDS WITH SAME TEXT PATTERN.

  • Select the cell where you want the result.
  • Put the function as =COUNTIF(F16:F27,”*IT*”)
  • Click OK.
  • The answer will appear as 8.

STEPS TO COUNT THE WORDS WITH WILDCARD CHARACTER.

  • Select the cell where you want the result.
  • Put the function as =COUNTIF(F16:F27,”L?T”)
  • Click OK.
  • The answer will appear as 1.

THE COMPARISON OF TEXT VALUE IN THE COUNTIF FUNCTION IGNORES THE CASE AND TREATS UPPER AND LOWER CASE AS EQUALS.

COUNT WORDS WITH SAME SUFFIX, PREFIX, PATTERN OR MISSING LETTERS
COUNT WORDS WITH SAME PREFIX, SUFFIX, PATTERN OR WILDCARD CHARACTERS

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

YOU MAY LIKE

PREVIOUS ARTICLE
NEXT ARTICLE

LEARN

Encourage us by following at …

Facebook Twitter Pinterest

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

Share this:

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

  • PICTURES ARE TOO SMALL?? Don’t worry, Click them, and pinch zoom to see them properly. All pictures are in high resolution.
  • KINDLY LIKE OUR FACEBOOK PAGE BY CLICKING HERE  AND IF WE NEED TO IMPROVE, KINDLY SEND US THE FEEDBACK ON [email protected]
  • Didn’t get what you were looking for?? Just put your question in the chat box present in the Right Lower corner. Name and email id [No spamming, just for the answer of your requirement] are needed.
*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: