Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Count How Many Times The Same Word Appears In Column

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


I have a long list of about 50,000 words in column A. Some of these
words are the same.

I want to know the exact number of times each word appears in the A

And I want to sort these words based on how many times they appear in
the A column.

How can I do that?


View Answers     

Similar Excel Tutorials

Dynamic Formulas that Update When you Add Data in Excel
I'll show you how to make formulas and functions that automatically update when more data is added to a range in E ...
Find the Next Blank Row with VBA Macros in Excel
Learn how to find the next empty cell in a range in Excel using VBA and Macros.  This method will skip any blanks ...
How to Enter Data into Excel
Entering data into Excel is exactly the same across all Excel versions and can be done in a number of different way ...
Introduction to Using Filters to Refine Data in Excel
Filtering allows you to hide rows of data which you are not interested in so that you can easily look at the rows y ...

Helpful Excel Macros

Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f
Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This UDF (user defined function) extracts the last word or characters from a cell in Excel. This is done by finding the
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f
Send Emails through Outlook using Email Addresses from Excel and text from Word
- This macro allows you to send an email to a list of recipients through excel. The email will be sent through Outlook an

Similar Topics

i have a single column of 200 four-letter words. I created a list, and found that there are about 12 different words is that group.

I need that list by itself in a column. How do i get that list off of the pull down?

I want to cut and paste just the list of 12 words, and also count how many times each word appears in that list of 200. Currently, i have to type out each of the 12 words, sort the column, then count manualy how many times each word appears! seems lenght!



i need to do one project for a university, and i can't get the formula right... hope somebody can help me out, would appreciate that alot!

situation is this. I have two text articles and have all their words put to column a with a count of how many times each word appears in all article (see attachmen), columns A and B.

Then respectively in columns GH and IJ there are words from each article and number, how many times each word appears in article.

I need to come up with a formula, to get numbers in indicated in red (in the attachment) which checks if the word on column A exists in Article 1 and if yes, how many times.

Appreciate your help!!

Hi, if anyone could help with a formula it would be greatly appreciated.
I need to extract data from a huge list of English words onto a smaller list of words.
The problem I have is that many of the words appear more than once on the big list with different attributes. For example, the word "still" appears four times as an adjective, a noun, a verb and an adverb. I need to be able to extract the data for each instance the words appear. Most appear once, many twice and a few three or more times.
I have a workbook with two sheets in. In the first sheet I have 60024 rows of data. Column A is the words and the other columns are the associated data. In the second sheet I have 3357 rows. Column A is the words, the next column is the word frequency and the final column is the word type. The problem I have with using Vlookup is that it only returns the first result of the words in the big list.


If I have a very long list of words within column A, many of which repeat, is there anyway that excel can tabulate the amount of times a particular word appears? I've used:


And dragged it down from B1 - however this does not fully meet my needs. Many of the words are a large string of letters which can be very similar to others and being able to recognise when it's analysing a new word is a little difficult. Can excel tabulate or export to a .txt file a table that would contain 1 column as 'Word' and another column as 'Hit count' thus sorting the data for me.

Thank you!


I am total newbie when it comes to working with excel and I would appreciate if someone could help me with the following two problems I am having -

1. In a single column I have a list of random words e.g in column A, I have 1000 words going vertically down, in columns 1 down to a 1000. I would like to know how I can add a comma to the end of each word automatically?

2. In column A I have a list of 2,609 words going vertically down (the amount of words could be any random amount - even and odd numbers). In column A for the first 20 words I would like to automatically put the words "GROUP 001" in Column B next to the first 20 words in column A, thus the words "GROUP 001" would be repeated 20 times in column B. For the next 20 words in line in Column A, I would like to put the words "GROUP 002" in Column B, thus the words "GROUP 002" would be repeated 20 times in column B. In this example there are 2,609 words so 2,609 divided by 20 = 130.45, so there would be GROUP 001 to GROUP 131 in column B.

I hope this is not to much of a challenge.
Thanks in advance

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 9">****** name="Originator" content="Microsoft Word 9">

Hello out there -
Here i am asking for more asistance, again.

I would like to copy the words in one column repeat them 8 times each and add a number to the end of each word that increments. The revised list can stay in the same column, just need to be able to do how ever many words are in the original column.
Thanks in advance--


Any Word Any Word 100 New Word Any Word 200 Next word Any Word 300 Any Word 400 Any Word 500 Any Word 600 Any Word 700 Any Word 800 New Word 100 New Word 200 New Word 300 New Word 400 New Word 500 New Word 600 New Word 700 New Word 800 Next Word 100 Next Word 200 Next Word 300 Next Word 400 Next Word 500 Next Word 600 Next Word 700 Next Word 800

I have a spreadsheet full of data that I only want certain parts of. Say I have column A full of different data, with 4 different words, but 100 rows of data.
I also have column E which has 12 different words and again 100 rows of data

What I have is 2 graphs.

1 shows information from Column A. It has the 4 different words and the number of instances of that word. So for example, word A has a value of 50, the other 3 make up the remaining 100

The second chart shows the 12 different words from Column E and again the number of instances they appear in column E. So example word Z appears 12 times.

What I want is another graph that shows word A (from column A) broken up. I want it to show what words (if you like) from column E to make up word A. A very confusing way to put it I know, but I tried.

So I have created a new page and I am currently trying to get the values that will make up my graph. But what is the formula I would use?

For example, for the second graph I use =countif(Sheet!E:E, "Word")

How do I change this to get what I need?


Hello I have a large text file and want to count the number of times each word appears. I have imported the data, using spacing as my deliminator, which more or less worked. Does anyone know a way that I could program it to count the number of times each word appears? Thanks


This is likely a pretty easy one. Attached is a spreadsheet example of what I'm trying to do. I have a column with words in it and I want to see how many times each word appears in the column. In the example attached I've put the numbers that the cell should return.



Hello everyone,

I have a long list of terms in the 'A' column. Each term contains several different words. What I would like to do is create a table that has two columns: One column contains each individual word that occurs within the 'A' column, and the Second column in the table would show the number of times that word occurs in in the 'A' column. This table should fill itself in with each word that has occurred in the 'A' column.

Hopefully I'm explaining myself well enough. I've attached a screenshot of the thing that I'm looking for. Thanks so much for any help!!!

item demonstration.jpg

hey, i need this text hep


B C D 1 Words in Sentence Repeated Times Sentence 2 I 2 I go to the cinema every month on last Sunday 3 Go 2 I go to work at 7 O'clock 4 Worl Cup will start on 2010

Excel tables to the web >> Excel Jeanie HTML 4

i want to convert all the words in column d by get one word from the column d and skip repeated

example word "I" mentioned in d2 and d3 so i get "I" in cell b2 then count it it mentioned two times in column D



I want to count the number of times a word appears.

i.e. in coloum C every time the word geography appears, science, English & Maths.

Many thanks,

Hello everyone,

I have a question here and wondering if somebody could help me out. I'm going to try to explain it the best I can. Here we go...

In column F, I have dates. Every cell down the column has different dates. For example: F1709 = 5/28/2008, F1710 = 5/19/2008, etc...

And in Column D, I have different words. For example: Cancellation, Transfer, Application, etc..

I'm trying to tell Excel to count everything in Column F that is between 5/1/2008 - 5/30/2008 that has a different word.

For example I need it to be like this:

Between 5/1/2008 and 5/30/2008 the word Cancellation appears 10 times, Application appears 23 times, etc...

I hope I have explained myself clearly and somebody can help me!!

Thanks everyone

I need help with an excel formula...

If a certain word appears in cells B2:B8, I want the number of times the word appears to be added up in cell A10

Let's say the word is dog and it appears 2 times in the specified cells, then the number 2 would be in cell A10.

Thank you

I have a problem to count some words in a column: in fact, in the cell they can write several words and my objective is to count in this column how many times there is the word "info" but as they can write several words in the same cell,(for example: speciality, info, degree) I can t really count how many times the word info is written in the column.

Is there a solution?


I need to build a way to check sequential characters.

I have 26 characters (alphabet) and a long list of 7 letter words.
I need a way to check each word for how often a group of letters appears in words, The result will be counts of three to six letters. Letters are not necessarily contiguous.Double letters are not counted.

The program would search all words for letters starting with a minimum of three letters in sequence.

The result would be how many times a sequence of letters occurs.

I think an easy way to do this would be to first sort the word into alphabetical order

for instance
bangles would become abeglns
aground would become adgnoru

searches would become:
abeglns: abe=1,abg=1,abl=1,abn=1,abs=1,aeg=1,ael=1,aen=1,aes=1,agl=1,agn=1,ags=1,aln=1,als=1,ans=1
adgnoru: adg=1,adn=1,ado=1,adr=1,adu=1, agn=2 ,ago=1,agr=1,agu=1,ano=1,anr=1,anu=1,aor=1,aou=1,aru=1

I have incremented agn as it appears in both words.

I thought maybe the best way to do this is in Excel as I do not know any programming and I also want to be able to sort to show sequences that appear a certain number of time.

Is Excel capable of doing this?
Can someone advise me how to do it?



I have put a formula in excel to count how many times the word 'administration' appears in a column:


Unfortunately, the output that I am searching has mulitple words in it, separated with a colon and no space. My formula skips the count if the word Administration is not completely on it's own

e.g. Administration counts 1
Administration;Cardiology does not count

Could anyone help me to adjust my formula to look for the word Administration if it is amongst other words?

Thank you in advance

I have a very long column of different words (eggs, bacon & tomatoes), There are about 3000 rows with these words. I am trying to count the number of times each word appears in sequence. For example A1:A4 is eggs then A5 :A11 is bacon etc etc. What I am trying to see is 4 eggs then 6 bacon. I have used countif but this gives me the totals, not the sequence? Not sure what to do here. Any ideas would be helpful. Cheers

Hi All,

I need to count the number times a specific word appears in a column but only when there is another specific word in a different column but on the same row....if that makes sense.

I would very much appreciate any help you can give.

Many Thanks

Hi All,

I need to determine the total number of words in a column and output the total to a different cell. Each cell will only ever have one word. I can do this with ASAP Utilities, but I need to be able to repeat this automatically as the word list changes. so I need a formula instead. Can someone please show me how to do this? I can find references online for how to count instances of a certain word, or duplicates, but nothing for the total words in a column. Thanks!

(Example of a list; generally with hundreds of words):

I got some fantastic help on this forum yesterday regarding how to count and identify the most frequently occurring words across rows of text.

I now have some added challenges to that same problem that I need some help with.

Attached is a sample Excel 2007 file containing 3 worksheets.

The Raw Data worksheet has the sample raw data. My real data is MUCH larger.

The Stop Words worksheet has a list of words that should not be included in the results. My actual list of Stop Words has about 600 words.

The Count Results worksheet is the final worksheet. In this sheet I'd like to do 2 things. First, I'd like to be able to enter a Maximum number of words to list. So, the user might enter the number 20 (in cell B1), as the Maximum number of words to list.

Second, I'd like to have the sorted list of most frequently occurring words in the Raw Data in one column, and in the next column I'd like to have the frequency with which the words occurred. In other words, the word that occurs most frequently would be listed first, along with the number of times it occurred; however, none of the Stop Words values will be included in this list.

I would really appreciate help with this!

I have a column of words, some of which are repeated. I want to make a chart showing how many times the words are repeated. Is there an easy way to do this? My first thought is to use COUNT to find out how many times each word is repeated, make a 2 column chart, and then graph that, but I feel as if there should be a much simpler way to do this. There are 50+ different words in this which makes my method incredibly inefficient. Any help would be greatly appreciated.

Dear Amazing Excel Team,
Say I had a list of words, all 9 letters long and I wanted to find all the words that had anagrams of themselves (e.g. ISOPTERAN - PATRONISE) and then list them separately as a new list.

I have tried all sorts of Excel spreadsheet solutions, but I always hit a dead end.

Can anyone write some code that I could add to a module that would accomplish this? The logic would go something like this:
Start with first word, check that word against all the words in the list (column of words), if it is an anagram with another word (e.g. ISOPTERAN - PATRONISE), then both words would be extracted and pasted in a new list for the first occurrence, if while still checking the first word in the list against the remaining words it found a second match, only the second word found would be extracted and pasted in the new list. After the first word was checked against all the words in the list the second word would be checked. Then the third, etc. until the whole list was checked.

Would someone be able to tell me how to easily summarize the occurance/number of times a word appears in a range. I trieed to use a pivot table or just a table and had no luck. The simplest example I can give is this if you have words in one column and you simply want to summarize how many times each word appears...

A1: Goat
A2: Cat
A3: Dog
A4: Goat
A5: Cat
A6: Cat
A7: Dog
A8: Goat
A9: Dog

Goat: 3
Cat: 3
Dog: 3

Thanks a lot

Need Urgent Help! - The number of cells that contains a particular text

I have a range of cells containing content and another range of cells containing just words.


Cell Range A:A1000 contains paragraphs/content

Cell Range B:B500 contain words/phrase. Each word occurs multiple times under column A. For instance the word 'great work' appears in multiple cells under column A, likewise there are hundreds of such words I have to find the cell references for from column A.

Please guide me how to make this possible.

It will be a great help!

Thank You,