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 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 ...
Get the First Word from a Cell in Excel
How to use a formula to get the first word from a cell in Excel. This works for a single cell and an entire list or ...
Change Specific Text within a Cell in Excel
Change or replace text in a cell with other text - you can replace a single character, numbers, letters, etc. This ...
Get the Last Row using VBA in Excel
How to find the last row of data using a Macro/VBA in Excel, including getting the number of that row and any data ...
Excel Prank - Automatically Change User-Entered Text
Use AutoCorrect to change the text a user enters into something funny. This is a great little prank to use on co-wo ...

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

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.


I have rows and rows of numbers and I'd like excel to count how many times numbers occur in the 40s, 30s, 20s and 10s

Row 1: 10 11 32 38 41 44
Row 2: 12 17 23 31 43 46
Row 3: 15 18 23 29 32 43

For the 40s:
The number of times a number occurs twice in the 40s is 2

the number of times a number occurs once in the 40s is 1

the number of times a number appears three times in the 40s is 0

For the 30s:
The number of times a 30s number appears once is 2

The number of times a 30s number appears twice is 1,

The number of times a number in the 30s appears three times or not at all in a row is 0

so as i have 6 columns of numbers, i'd like it to calculate how often numbers in the 40s, 30s, 20s, and 10s appear once, twice, three times, four times, five times, and six times in each row
Does anyone have any idea what formula I could use? Any help would really be appreciated.

I have a spreadsheet with about 6000 words in one column.
I would like to sort them in alphabetic order in another sheet:
First column in this worksheet (A) will host only words starts on A second column B will host only B words.,etc..
I can created formula which will do this by starting always from the first row in the Words spreadsheet but this always will leave empty cells:
Words in column B will start in row where A words have finished, C where B finished and so one.

Any help?

Is there a formula that will count the number of times a character or word appears in a cell? I have downloaded fields from a database where each item is separated by the word "and". For example the field would say Atlanta_and_Chicago_and_Detroit. I am trying to count the number of cities that show up in this field so in this example I would want it to return 3. Is there a simple formula to count the number of "and"s in a cell?

Thanks in advance

I have problem writing a code to open a word.doc and count the different number of words in the doc. my code is as follows. Can anyone tell me what is the prob?


Sub automateword()

    Set wordapp = CreateObject("word.Application")

    wordapp.Documents.Open "C:\Documents and Settings\Admin\Desktop\letter.doc"
    wordapp.Visible = True

Dim totalwords As Long

totalwords = Activeworksheet.Words.Count

Dim i As Long, Word As Range, str As String, j As Long, f As Integer

Dim arr() As String

For Each Word In ActiveDocument.Words
    For i = 1 To totalwords Step 1
    f = 0
        For j = i To totalwords Step 1
     If arr(i) = arr(j) Then
     f = f + 1
      Range("A1").Offset(i, 0).Value = Word
      Range("B1").Offset(i, 0).Value = f
    End If

End Sub

i have cells with data in the following form:

Cell Contents
A1 = 33
A2 = 334
A3 = 344
B1 = 444

is there a way that i could count how many times the number 4 and the number 3 is repeated in the whole area (eg. number 4 appears 6 times, number 3 appears 5 times)?

i'm looking forward to your thoughts on this.

I need to extract the last two words from strings, the words are not the same character length. Also each two words is after the word 'by' basically it's a product then says who it's by. So if anyone could help with a formula to extract the last two words or at least all words after the word 'by' I would really appreciate it.


I need help with a macro which creates random sentences out of a list of words (I have a column with 2000 rows. Each cell contains 50-60 words). In each cell, I would like to create sentences - without meaning - for example one sentence contains 4 words, the next one 8, the next one 5...
Basically the words should be separated randomly by a dot, and after each dot the next word has to begin with a capital letter.

Any assistance you could provide would be really appreciated.

Kind Regards

Hi folks

I am fiarly new to discussin boards and have a general question relating to Excel 07 please.

I wish to determine the number of words in a column that begin with a particular letter.

For example - Assume Cell A1 has the word DOG, A2 has CAT, A3 has CAR, A4 has TAR and A5 has PIG. Is there a formula that i can place into A6 that sums the number of words in the column from A1 to A5 that begin with the letter C. In this example the answer "2" would appear in A6 as there are 2 words in the list that commence with the letter "C".

Any feedback is greatly appreciated



I have two columns:

Column A1:A250 contains one word (e.g. a, an, but, etc) per cell for each of the 250 cells.

Column B1:B800 contains one text phrase (e.g. i am tall, i am a person, etc) per cell for each of the 800 cells

I am trying to determine if each text phrase in Column B contains any WHOLE word from Column A.

So in my example above, the sentence "i am tall" would return a False value since the sentence ""i am tall" does not contain any of the WHOLE words from column A (e.g. a, an, but). Assume for this example that Column A also does not contain the words "I," "am," or "tall" either.

On the other hand, "I am A Person," would return the value True since this sentence contains the whole "a" in Column A.

Also, if a phrase from Column B contains more than one word from Column A, it does not need to count the number of instances ... it simply needs to return the value True to indicate that at least one of the words from Column A is contained within the word from Column B.

Thanks for your help!

I hope i can explain clearly what i want to do. I have words in column A and i want the words from column A to replace a specific word in column B. For example i have these names in column A

Verlie Teresi
Aron Rouhoff
Marti Steve
Adalberto Ayala
Ossie Varin
Cristobal Furblur
Tess Wielgus

and i want every name to replace "name_here" in this text - This is "name_here" document. How to do this?


I want to know if excel can underline misspelled words like Word? If not is
there any way to turn off autocorrect and hightlight misspelled words in
excel or word. With auto correct on if I type a word or name that the
application thinks is missed spelled but isn't it changes it. I don't want
the word changed but highlighted or underlined so I can have an idea of
possible misspelled words.

Hello, I'm trying to count the number of times a name appears across multiple worksheets but I can't seem to figure it out.

I have 6 sheets to count and all the names are in column A (from A1:A100) on each sheet. The names are not in a particular order.

On sheet 7 I want to have a cell beside each persons name that counts the number of times their name appeared on the other 6 sheets.

For example Sheet 7 (called total) would have

Joe 5 (where 5 is the outcome of the formula I'm looking for to count all the times "Joe" appears on sheets 1-6).

I have it counting per sheet with =COUNTIF(A1:A130,"name") but this is not quite what I am looking for.

Thanks in advance for any advice.

Edit: I am using Excel 2007 w/ windows XP


I have data in column A which looks like below


Here is what I am trying to determine

1 appears with 1 in 0 rows
1 appears with 2 in 2 rows
1 appears with 3 in 0 rows
1 appears with 4 in 0 rows
1 appears with 5 in 2 rows

and so on.

I can have the table setup in different column as

Value 1, Value 1, Count
Value 1, Value 2, Count


Hope this makes sense

Hi there. You know how if you begin typing a list say in column A, & you start to type a word(s) that you had typed before, Excel automatically shows that word(s) & you can just press enter to accept it???

Here's what I'd like to be able to do. Set up a list of words in column A on sheet2 & as a user begins to type words(s) in column A of sheet1, inuitively show that word(s) from sheet2 so that the user can just press enter to accept. Does this make sense?

Thanks for any suggestions

Hi all. I have a list of about 100 rows that have the old term in column A and the new term that I would like it replaced with in column B. I have another column with about 3,000 cells that I would like to replace all of the old words in column A with the new words in column B.

Is this possible with doing the find/replace tool manually 100 times? Any help would be greatly appreciated.

I need VBA code to findout how many times a word "ACTIVE" has occured in given range. But the issue is, there is one filter applied on the next column and I want to count the occurance of given word in visible rows only. It should not count the words from non-visible rows.

Please assist with the VBA code for this.

Thanks in advance. (I will be leaving form the office, I will revert to the comments by tomorrow morning.)

Hi there,

I have a column filled with company names. I need to remove specific words from the cells in the column such as 'the', 'of', 'university' etc. Does anyone know if it is possible to do this using a formula in Excel 2003? I thought I could maybe record a macro and use find/replace but it was removing those strings of letters where they occured within a word too (e.g. 'the' was removed from the word 'southern' leaving the mangled word 'sourn').

A bit of context in case this helps... I have data which I want to match with this list of company names using a lookup function. At present I'm only getting a result where the cell contents match exactly. So, I want to remove as many extraneous words as possible to increase the possibility of getting a match.

Many thanks in advance for any help people can offer.

All the best


Hi all,

I'm working on a survey response, that includes around 10000 sets of replies to q's. Most of these are yes/no, tickbox replies which I can analyse without a problem...

However - some of the questions are qualitative, with one in particular leaving the respondent to write what their favourite holiday destination is. As a set list of places wasn't given as an option, there are hundreds of different types of reply.

Does anyone know how to count the number of instances of a word in Excel - so for eg the number of times Paris appears etc....

The only option I can think of is to literally write a list of places, and then go through the responses one by one adding them up... but this will take ages....

Any help appreciated!


I have a problem when i have to find duplicating words in two columns. It doesn't highlight the duplicated words if they are in combination with other words. For example, if in cell A1 is the word "diet plan" and in the cell B2 is the word "diet recipe" it won't highlight the word "diet" as a duplicate. I am using this way to find duplicates - Conditional Formatting>Highlight cells rules>Duplicate values... but it doesn't work.

I want to highlight all type words from column A that have duplicates anywhere in column B and not just the words that are in the same rows.

How to do this?



I may need just some nomenclature or switch

I have named a range of "find text" in a column.
I am searcing a "within text" column of cells, each with a text string.
I want to return match / no match for exact phrases, in exact order, NOT case sensitive.
I also want to continue getting a match for sincle words in the "find text" column (list).

Currently I get a match if a complete find text word is found, which is good since I don't want partial words or compound words in the text strings to match.

BUT if there are two or more words in my find text phrase,
I get a match even if one word is in the "within text" column.

For instance:

find text:
brown cow
yellow cow

within text:
the cow jumped over the moon
2) the brown cow is a funny cow
the cow is mellow, the one that is yellow
brown and yellow are the names of my cows
5) shitbrown paddies line the cowyard
my son's hair has a brown cowlick.

I ONLY want to "match" for string example #2,
AND I want "No Match" for all the others.

Currently all of these except #5 match.

Also I have two ranges searching right now such as

Brown Cow
Yellow Cow

Red Cow
Blue Cow

and the formula I am using is this:

=IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&CowsILike&" "," "&A4&" "),CowsILike)),IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&CowsIDon'tLike&" "," "&A4&" "),CowsIDon'tLike)),"MAYBECowsI'llConsiderLiking","CowsIDOn'tLike"),"CowsILike")



Hello everyone,

I need some assistance please. I need a formula for calculating the density of words in a column. I'm thinking this would involve some sort of VLOOKUP.

It will need to count every instance of each keyword and every phrase variation. Then it will determine the density of each one.

For example: "Totally Free DVD Players" This is the reference, it would be column A

Word Counts would be in Column B

1 Word Counts

2 Word Counts
Totally Free
Free DVD
DVD Players

3 Word Counts
Totally Free DVD
Free DVD Players

4 Word Counts
Totally Free DVD Players

Let's assume that there are 100 cells from A 2 - A 101. The formula would need to capture somehow every word in the spreadsheet, reference against column A, and determine the density for every phrase starting with 1 word.

Example: The title of a Web page is 'Get Best XYZ Services'. Keyword Density for 'XYZ services' is 2*1/4*100%=50%. If you reduce the number of words in the title by removing the word 'get', so the title becomes 'Best XYZ Services', than the keyword weight will be larger: 2*1/3*100%=67%

Finally, I need to get an average for each density in the spreadsheet. I'd like this summary report to be created in a new worksheet.

I have link to a webpage that helps explain it better:

Well, I guess that's it. Thanks in advance for any suggestions

i currently use a macro that outputs each record of a merged document as a text file, but what we do is add the word "end "
to the last entry in the excel data source
the word macro loops through each record until it sees the word "end" then stops! but with an error ,well that ok but its messy, how can i change it so that once the last record is found it stops!(a message box with "20 generated" or similar would be nice!
here's the code.


Sub Createtext()

 ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
'Click the button to select the next record if in manual mode
 a1 = ActiveDocument.Words(2).Text
 a2 = ActiveDocument.Words(3).Text
 a3 = ActiveDocument.Words(4).Text
 a4 = ActiveDocument.Words(5).Text
 a5 = ActiveDocument.Words(6).Text
 a6 = ActiveDocument.Words(7).Text
 a7 = ActiveDocument.Words(8).Text

'Select the second word of document as reconstructed filename

 ActiveDocument.SaveAs FileName:=a1 + a2 + a3 + a4 + a5 + a6 + a7 + ".txt", FileFormat:=wdFormatDOSText

'Save the text file
Loop Until a1 = "end"      ' The last record must contain "end"
End Sub


I want to have a formula in a cell that counts the number of time a certain
word appears in any cell in a worksheet. i've tried using count but it only
counts for numbers, not text.