| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting number of occurances of a word
I need to count the number of times a word shows up in a given cell for which
the number will then show up below the name of the word. ex. B1 [Jon] A3 ? How many times did Jon appear? [Jon, Kate, Jon, Lilly] I want excel to put in a number where the ? is. Please help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of occurances of a word
[Jon, Kate, Jon, Lilly]
Do the brackets appear in the cell? Are the names always separated by commas? -- Biff Microsoft Excel MVP "Chris the researcher" <Chris the wrote in message ... I need to count the number of times a word shows up in a given cell for which the number will then show up below the name of the word. ex. B1 [Jon] A3 ? How many times did Jon appear? [Jon, Kate, Jon, Lilly] I want excel to put in a number where the ? is. Please help! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of occurances of a word
From the below example; it looks like the names are separated by a comma.
=(LEN(A3)-LEN(SUBSTITUTE(A3,"Jon" & ",",",")))/LEN("Jon") with Jon in B1 and the text string in A3... =(LEN(A3)-LEN(SUBSTITUTE(A3,B1 & ",",",")))/LEN(B1) If this post helps click Yes --------------- Jacob Skaria "Chris the researcher" wrote: I need to count the number of times a word shows up in a given cell for which the number will then show up below the name of the word. ex. B1 [Jon] A3 ? How many times did Jon appear? [Jon, Kate, Jon, Lilly] I want excel to put in a number where the ? is. Please help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of occurances of a word
With string in A3 and name to count in B1, try this:
=(LEN(A3)-LEN(SUBSTITUTE(UPPER(A3),UPPER(B1),"")))/LEN(B1) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Chris the researcher" <Chris the wrote in message ... I need to count the number of times a word shows up in a given cell for which the number will then show up below the name of the word. ex. B1 [Jon] A3 ? How many times did Jon appear? [Jon, Kate, Jon, Lilly] I want excel to put in a number where the ? is. Please help! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of occurances of a word
Thank you, this is helpful, however it gets more complicated now.
I have a series of words in each cell in a column AN. The cells go from AN16-AN200. Each cell has a variable amount of words that are separated by commas. I need to know how many times a word appears in that cell and I need to put that number in another cell. ex. AN 16-COMP1,HNF,COUPTF MAZR,COUPTF E2F,MAZ,E2F-1,Pax-6,Pax-3,myogenin/NF-1,KROX,COMP1,E2F-1,Sp3,Muscle In another column AX16, I need to insert the number of times COMP1 occurs. I also need to be able to scroll the formula down to all of the cells that might have COMP1 in them and put in the number in the corresponding AX column. Thank you! "Jacob Skaria" wrote: From the below example; it looks like the names are separated by a comma. =(LEN(A3)-LEN(SUBSTITUTE(A3,"Jon" & ",",",")))/LEN("Jon") with Jon in B1 and the text string in A3... =(LEN(A3)-LEN(SUBSTITUTE(A3,B1 & ",",",")))/LEN(B1) If this post helps click Yes --------------- Jacob Skaria "Chris the researcher" wrote: I need to count the number of times a word shows up in a given cell for which the number will then show up below the name of the word. ex. B1 [Jon] A3 ? How many times did Jon appear? [Jon, Kate, Jon, Lilly] I want excel to put in a number where the ? is. Please help! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of occurances of a word
It's not really more complicated.
Just adjust your cell addresses. With the "word" you're looking to count entered in B1 (COMP1), enter this formula in AX16: =(LEN(AN16)-LEN(SUBSTITUTE(UPPER(AN16),UPPER(B$1),"")))/LEN(B$1) And copy down as needed. The inclusion of the Upper() function allows you to use "comp1" in B1, and still count any combination of upper and lower case characters in your Column AN, as long as they spell out "comp1". -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Chris the researcher" wrote in message ... Thank you, this is helpful, however it gets more complicated now. I have a series of words in each cell in a column AN. The cells go from AN16-AN200. Each cell has a variable amount of words that are separated by commas. I need to know how many times a word appears in that cell and I need to put that number in another cell. ex. AN 16-COMP1,HNF,COUPTF MAZR,COUPTF E2F,MAZ,E2F-1,Pax-6,Pax-3,myogenin/NF-1,KROX,COMP1,E2F-1,Sp3,Muscle In another column AX16, I need to insert the number of times COMP1 occurs. I also need to be able to scroll the formula down to all of the cells that might have COMP1 in them and put in the number in the corresponding AX column. Thank you! "Jacob Skaria" wrote: From the below example; it looks like the names are separated by a comma. =(LEN(A3)-LEN(SUBSTITUTE(A3,"Jon" & ",",",")))/LEN("Jon") with Jon in B1 and the text string in A3... =(LEN(A3)-LEN(SUBSTITUTE(A3,B1 & ",",",")))/LEN(B1) If this post helps click Yes --------------- Jacob Skaria "Chris the researcher" wrote: I need to count the number of times a word shows up in a given cell for which the number will then show up below the name of the word. ex. B1 [Jon] A3 ? How many times did Jon appear? [Jon, Kate, Jon, Lilly] I want excel to put in a number where the ? is. Please help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting occurances | New Users to Excel | |||
| Counting 7 and 8 occurances | Excel Discussion (Misc queries) | |||
| Counting Occurances | Excel Discussion (Misc queries) | |||
| counting occurances | Excel Worksheet Functions | |||
| count number of occurances of a word in a range | Excel Worksheet Functions | |||
Linear Mode
