Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default counting the number of times a word appears

I have over 1000 entries where one colum contains cells with multiple info
such as:
Provide professional development opportunities, Subsidise the cost of
training courses, Organise social events, such as presentation nights,
dinners, thanks BBQs or breakfasts,

Is there any simple way to count the number of times a word e.g.
'professional' appears in the 1000 cells in that column?

I know how to count if there is just one word or phrase in each cell, the
problem is occuring because there are multiple prhrases in each cell.

Any help you could provide would be great.

Cheers
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default counting the number of times a word appears

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LEN(A1:A1000) - LEN(SUBSTITUTE(A1:A100, "professional", ""))) /
LEN("professional")

or, if "professional was in B1:

=SUM(LEN(A1:A100) - LEN(SUBSTITUTE(A1:A100,B1,"")))/LEN(B1)


In article ,
Adrienne <Adrienne @discussions.microsoft.com wrote:

I have over 1000 entries where one colum contains cells with multiple info
such as:
Provide professional development opportunities, Subsidise the cost of
training courses, Organise social events, such as presentation nights,
dinners, thanks BBQs or breakfasts,

Is there any simple way to count the number of times a word e.g.
'professional' appears in the 1000 cells in that column?

I know how to count if there is just one word or phrase in each cell, the
problem is occuring because there are multiple prhrases in each cell.

Any help you could provide would be great.

Cheers

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default counting the number of times a word appears

Ooops - all the ranges should be the same length, e.g., A1:A1000

In article ,
JE McGimpsey wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LEN(A1:A1000) - LEN(SUBSTITUTE(A1:A100, "professional", ""))) /
LEN("professional")

or, if "professional was in B1:

=SUM(LEN(A1:A100) - LEN(SUBSTITUTE(A1:A100,B1,"")))/LEN(B1)

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default counting the number of times a word appears

Here are some ideas:

H1 = some word

=COUNTIF(A1:A20,"*"&H1&"*")

However, this is susceptible to "false positives". For example, if the word
to count was profession the above formula would count professional.

This formula is more robust (but not "bulletproof") against "false
positives":

H1 = some word

=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&H1&" "," "&A1:A20&" "))))

For example, if the word to count was profession and the string in the cell
contained profession, (profession<comma) the above formula would not count
profession<comma.

Also, do you want to count *every instance* of the word or just the number
of cells that contain the word? For example, profession appears in cell A1
twice. Should that count as 1 or 2? The formula to count every instance
would also be susceptible to "false positives".

--
Biff
Microsoft Excel MVP


"Adrienne" <Adrienne @discussions.microsoft.com wrote in message
...
I have over 1000 entries where one colum contains cells with multiple info
such as:
Provide professional development opportunities, Subsidise the cost of
training courses, Organise social events, such as presentation nights,
dinners, thanks BBQs or breakfasts,

Is there any simple way to count the number of times a word e.g.
'professional' appears in the 1000 cells in that column?

I know how to count if there is just one word or phrase in each cell, the
problem is occuring because there are multiple prhrases in each cell.

Any help you could provide would be great.

Cheers



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default counting the number of times a word appears

Thanks so much for your help.

I was worried the answers would be extremely confusing, but I understood
yours and it worked. So thanks.

"T. Valko" wrote:

Here are some ideas:

H1 = some word

=COUNTIF(A1:A20,"*"&H1&"*")

However, this is susceptible to "false positives". For example, if the word
to count was profession the above formula would count professional.

This formula is more robust (but not "bulletproof") against "false
positives":

H1 = some word

=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&H1&" "," "&A1:A20&" "))))

For example, if the word to count was profession and the string in the cell
contained profession, (profession<comma) the above formula would not count
profession<comma.

Also, do you want to count *every instance* of the word or just the number
of cells that contain the word? For example, profession appears in cell A1
twice. Should that count as 1 or 2? The formula to count every instance
would also be susceptible to "false positives".

--
Biff
Microsoft Excel MVP


"Adrienne" <Adrienne @discussions.microsoft.com wrote in message
...
I have over 1000 entries where one colum contains cells with multiple info
such as:
Provide professional development opportunities, Subsidise the cost of
training courses, Organise social events, such as presentation nights,
dinners, thanks BBQs or breakfasts,

Is there any simple way to count the number of times a word e.g.
'professional' appears in the 1000 cells in that column?

I know how to count if there is just one word or phrase in each cell, the
problem is occuring because there are multiple prhrases in each cell.

Any help you could provide would be great.

Cheers






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default counting the number of times a word appears

Glad it worked for you. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Adrienne" wrote in message
...
Thanks so much for your help.

I was worried the answers would be extremely confusing, but I understood
yours and it worked. So thanks.

"T. Valko" wrote:

Here are some ideas:

H1 = some word

=COUNTIF(A1:A20,"*"&H1&"*")

However, this is susceptible to "false positives". For example, if the
word
to count was profession the above formula would count professional.

This formula is more robust (but not "bulletproof") against "false
positives":

H1 = some word

=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&H1&" "," "&A1:A20&" "))))

For example, if the word to count was profession and the string in the
cell
contained profession, (profession<comma) the above formula would not
count
profession<comma.

Also, do you want to count *every instance* of the word or just the
number
of cells that contain the word? For example, profession appears in cell
A1
twice. Should that count as 1 or 2? The formula to count every instance
would also be susceptible to "false positives".

--
Biff
Microsoft Excel MVP


"Adrienne" <Adrienne @discussions.microsoft.com wrote in message
...
I have over 1000 entries where one colum contains cells with multiple
info
such as:
Provide professional development opportunities, Subsidise the cost of
training courses, Organise social events, such as presentation nights,
dinners, thanks BBQs or breakfasts,

Is there any simple way to count the number of times a word e.g.
'professional' appears in the 1000 cells in that column?

I know how to count if there is just one word or phrase in each cell,
the
problem is occuring because there are multiple prhrases in each cell.

Any help you could provide would be great.

Cheers






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
counting the number of times value of "x" appears in a row Hugh Excel Discussion (Misc queries) 2 August 2nd 06 12:17 PM
count the number of times a specific word appears in a column BAR Excel Worksheet Functions 1 June 27th 06 05:03 PM
Counting the number of times a word appears 'anywhere' on a page Brother Laz New Users to Excel 1 June 17th 06 03:06 PM
Counting the number of times a specific character appears in a cell PCLIVE Excel Worksheet Functions 3 November 4th 05 06:08 AM
Counting the number of times a word appears in a worksheet Jig Bhakta Excel Worksheet Functions 4 February 3rd 05 04:01 AM


All times are GMT +1. The time now is 06:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"