Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to count number of text occurences in Excel column

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 09-06-2009
Member
 
Join Date: Feb 2009
Posts: 55
How to count number of text occurences in Excel column
  

hie,
I want to count the number of occurences of a text string in an Excel column. Does anybody has any method or formula to do the same ?
Please suggest.... thank you

Reply With Quote
  #2  
Old 09-06-2009
Member
 
Join Date: May 2008
Posts: 4,345
Re: How to count number of text occurences in Excel column

The formula to count the number of occurrences of a text string in a cell range is given by -
=SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")

where,
range - specifies the selected cell range.
"text" - specifies the particular text string that you want to count.
Reply With Quote
  #3  
Old 09-06-2009
Member
 
Join Date: May 2008
Posts: 4,831
Re: How to count number of text occurences in Excel column

If you want to count the occurence of the text string say for example, "word" in the cell range of "A1 to A20", then try this ::
=COUNTIF(A1:A20;"word")
Reply With Quote
  #4  
Old 09-06-2009
Member
 
Join Date: Apr 2008
Posts: 4,642
Re: How to count number of text occurences in Excel column

Start Excel
Open a new workbook.
Type the following on sheet1:

A1: Fruit
A2: apple
A3: orange
A4: grape
A5: apple,apple
A6: orange,orange
A7: grape,grape
A8: apple,orange
A9: orange,grape
A10: grape, apple
A11: =SUM(LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,"orange","")))/LEN("orange")

The value of cell A11 will be 5 because the text "orange" appears five times in the cell range A2 to A10...
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to count number of text occurences in Excel column"
Thread Thread Starter Forum Replies Last Post
How to convert number to text in excel Jevin Software Development 9 17-05-2012 06:42 PM
How to count number of characters in Excel Bankebihari Windows Software 6 15-05-2012 04:52 PM
How to replace column name from numbers to text in Microsoft Excel Talisha MS Office Support 2 17-02-2012 01:03 PM
Count the number of times yes appears in a range in excel Andy Candy Windows Software 3 17-08-2009 12:33 PM
How to check if a number belongs to a column or not in Excel $tatic Software Development 4 07-04-2009 09:45 PM


All times are GMT +5.5. The time now is 03:05 PM.