Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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

Determine if Cells Contain a Specific Value in Excel
Find if a cell or range of cells contains a specific value in Excel. This method can be used on individual cells wh ...
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 ...
Open Microsoft Word from Excel
This free macro will open the Microsoft Word program on your computer. You do need to have this program first. Thi ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...

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!!

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


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,

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 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

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!

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.

Hi! Can someone help me?

I have a list of strings of letters (column A):


And then a list of words (column B):

The words from the second list are hidden in the strings in the first list (I marked them red). The words can be hidden or more than once in the whole list, or even in the same string.

I need a formula in column C which will go through the whole list of words B, and for each of the words, return: how many times the word is hidden in the whole list, and in which string(s).

Any ideas?


I am doing a task where a macro that searches a word where a box pops up appears then you can search the word in column D. The word can occur multiple times in the column. It finds that word it deletes the contents in the row from the cells from bc to gg. This macro should keep all of the text before rows bc and only delete the words in a rows from bc on where the word is found. Please help me with this. Thank you

I want to count the number of times a row has two specific words, in two
different cells eg.
If column A contains the word 'Widget' and column B contains the word 'Sale'
I want to count the number of times this occurs.

I'm sure this is easy but I've spent hours trying to get it to work.

I have long column of 3 or 4 word phrases.
gas mileage calculator
trip it
trip calculator
gas trip calculator
mph calculator
gas cost calculator
gas estimator
gas buddies

I have taken this column, and split cells so each word is in it's own row, and then manually put them into a new column and removed duplicates giving me a unique list of all the words in my original column without dupes.

So I would have:

I have then added a formula to try and count how many times each word occurs in my original column word list.
Using Formula:

Please Login or Register  to view this content.

BUT it mostly works, but it also counts partial matches. so it would count "fuel" as occurring in "fuelmileage" when it should only count it if the word is "fuel mileage". i.e. fuel is a seperate word.

(if I replace "*"&Q6&"*" with "*fuel*" in the formula it only counts whole word matches and it works, but I need it to grab the data from a cell reference.

Any ideas on how to fix my formula nicely so it only find whole matches?

I have a Col B called should this be included.
How would I get it to only count word occurrences based on a different column where the col B value = YES.

Those should be the easy ones...leading me to my trickiest question!

In col A, I have a list of 2 or 3 word phrases.
red rabbit
hunting red rabbit
blue rabbit

In Col D I have a list of 20 single words. (1 per row)

I would like to add a check to see if ALL of the words in col A are found in my master column of single words.
I.e. Let's say cell A:1 = "red rabbit"
and in my master list I have "red" and "rabbit" I would like it to see that ALL words in A:1 were found in master list and thus can return a TRUE value, whereas if A:2 is "hunting red rabbit" it would return a false since it didn't find the word "hunting" in the master list.

Hi everyone. I have a very ambitious project that I am working on. There are many stages to it and it may well melt my CPU! However, I must first make a few tests and for these, some advice would be of great value, if you can help.

First of all, I need to be able to index all of the words in a large selection of txt files. Each unique word needs to be placed in a cell in a big long column. I expect there will be tens of thousands of words in all of the text files so I mean a REALLY long column. I also need to count the number of incidences of each word over all the txt files and have this "count" as a separate column.

The next step will be to remove words that are superfluous like "the, it, and ... etc" and words that are so rare that they do not provide any useful information. This, hopefully, will leave me with a list of common keywords from the txt files.

This will then need to be put into alphabetical order!

Presently, the txt files are in pdf form so I have a little bit of work ahead of me before I can begin. Any advice on how to index words in the way described above would be most valuable.

I am realising more and more, that a good bit of skill with macro-programming will go a long way if I am using Excel for stuff like this. Can anyone suggest a good book that might help?

Many thanks.


Hi guys,

This one is tricky, I guess...

In column A we have words, like this:


In column B we have another list of words:


I need a formula in C1 which will check all the words from column B and find the ones which are anagrams of some of the words from column A, and then return those words next to them.

(Anagram is the word made up of another word's letters, without repeating or omitting any, for example anagram of SPARK is PARKS).

In the example above, we would have the words: EXCEPT next to EXPECT (in C3), OUTSIDE next to TEDIOUS (C5), and STREET next to SETTER (C7).

Any ideas?

Cell A1 contains a paragraph made of Hebrew words (They are Bible verses in Hebrew, so sometimes more than 254 characters long in each cell).
Cell B1 contains a paragraph of English words (They are Bible verses in English, so sometimes more than 254 characters long in each cell).
Column C contains a list of single Hebrew words.
Column D contains a unique code for each of those Hebrew words.
Column E contains a list of single English words.
Column F contains a unique code for each of those English words.
Finally, Column G contains a list of single words that will be replacing the English words IF all criteria is met.

So, this is what I'm trying to do:
IF a Hebrew word within the paragraph of cell A1, is the same as a Hebrew word found in the list in Column C,
AND an English word within the paragraph of cell B1, is the same as an English word found in the list in Column E.
AND the unique code for this word is the same in (both) Column D and Column F,
THEN create a new paragraph (Bible verse) in a (new) Column H, which will be a combination: Rendering the existing English words from B1 that did not meet this criteria, but rendering the transliterated words from Column G when all criteria is met.
I'm not sure Excel will support this task because of the 254 character issue, but since I don't know VBA, I figure getting help in Excel will be easier.

Thank you kindly,

I know the title is confusing, but I don't kow how to describe this otherwise.

Here is the challenge I'm facing:

I have a list of synonyms in word in the following format:

word A1 Word A2 Word A3 (<-- all A-words are synonyms from each other on the same row)
Word B1 Word B2 Word B3 Word B4 (<-- all B-words are synonyms from each other on the same row)

Now I need the following output:
Word A1 Word A2
Word A1 Word A3
Word A2 Word A1
Word A2 Word A3
Word A3 Word A1
Word A3 Word A2
Word B1 Word B2
Word B1 Word B3
Word B1 Word B4
Word B2 Word B1
Word B2 Word B3
Word B2 Word B4
Word B3 Word B1
Word B3 Word B2
Word B3 Word B4
Word B4 Word B1
Word B4 Word B2
Word B4 Word B3

If I try to explain it in words: I need all combinations of synonyms in pairs formatted in rows.

I need this for a piece of software which allows import of synonyms, but the format has to be as above (each synonym on a row, in pairs).

How would I accomplish this in Excel?

I've uploaded an excel file with an example and on the second worksheet the complete synonym list (in foreign language, but that shouldn't matter)

You can find it he

I would appreciate it very much if someone can offer me a solution.

Kind regards,


my formula:

this formula counts the number of times keyword from column A, appears in column B

the downside of the formula is that it wont make the count for keywords if they are not separated by a space. so, i'll need help from you to make the words not separated by space to also count.

see the 4th and 5th line (especially) from table below to see what i mean

column a column b column c one two three one two three four five 1 one two three one three two four five 0 one two three one two three four five, one two three 2 one two three one two three four five, one two three something, one two three 3 one two three onetwothreefourfive, one two three something, one two three 3