Count in a cell where there is several words...

**koda86** · 12-14-2006, 07:18 AM

Hi,
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?

Thanks

**vikas.bhandari** · 12-14-2006, 07:45 AM

Hello,

use this :

Countif(A:A,"*info*")

please note, it will only count the no of cells which contains "Info" but it will not tell how many times this word is appearng in a single cell??

Does this help??

Thanks,
Vikas Bhandari

**koda86** · 12-14-2006, 08:03 AM

This helps me perfectly!!
Cheers

**Bryan Hessey** · 12-14-2006, 10:17 AM

Originally Posted by koda86

This helps me perfectly!!
Cheers

Hi,

Manually, you can Replace All, Info for Info to get a count of the words. (But I cannot find a way to get that count in VB or Formula)

hth
---

**vikas.bhandari** · 12-18-2006, 03:14 AM

Originally Posted by Bryan Hessey

Hi,

Manually, you can Replace All, Info for Info to get a count of the words. (But I cannot find a way to get that count in VB or Formula)

hth
---

Umm, yeah that can be an option, but we dont have to run a Macro, then we have no other option than countIf. Also, I think I didnt understand what you wantd to say....you want to say tht in VB, we cannot find how many times word "Info" is coming in a string???

Regards,
Vikas Bhandari

**Bryan Hessey** · 12-18-2006, 10:36 AM

Originally Posted by vikas.bhandari

Umm, yeah that can be an option, but we dont have to run a Macro, then we have no other option than countIf. Also, I think I didnt understand what you wantd to say....you want to say tht in VB, we cannot find how many times word "Info" is coming in a string???

Regards,
Vikas Bhandari

in Manual mode you can 'Replace All' and receive a count of words that you replaced. In VB you can Replace All, but I see no method of picking up the count displayed to the manual display.
It is possible to character search text for any information, but that could be noticeable in it's slowness, and would be more difficult to organise that the Replace All.

It would depend upon the effort you wanted to put in to write code that is already performed on a manual basis.

Does this help you?
---

**daddylonglegs** · 12-18-2006, 04:24 PM

I guess the OP is happy with

Countif(A:A,"*info*")

but if you want to count the number of times "info" appears in A1:A10, including multiple times in a cell the you could use this formula

=SUMPRODUCT(--((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"info","")))/LEN("info")))

**Bryan Hessey** · 12-18-2006, 09:44 PM

Originally Posted by daddylonglegs

I guess the OP is happy with

Countif(A:A,"*info*")

but if you want to count the number of times "info" appears in A1:A10, including multiple times in a cell the you could use this formula

=SUMPRODUCT(--((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"info","")))/LEN("info")))

we can now, very nice twist daddylonglegs

---

**vikas.bhandari** · 12-19-2006, 02:46 AM

Man, you are crazzzzyyyyyy...how do you do all this!!!

Well, just a feedback....this is an awesome forum where I get a lots of stuffs to learn and a chance to discuss with the most intelligent creatures in this world!!!

Cheers to Excel Forums

Thanks to every moderator and other intelligent personalities around!!!!
Vikas B

**Dav** · 12-19-2006, 08:54 AM

A slight word of caution if info is contained in another word eg information it will be counted by all the formulas suggested. I do not have an easy answer to overcome this, but thought you should be aware.

Regards

Dav

**daddylonglegs** · 12-19-2006, 07:09 PM

Perhaps you can't accommodate all punctuation but assuming words in A1:A10 are separated by just a comma, a space or a combination of the two then this formula should count all instances of "info" but not "inform" or "information"

=SUMPRODUCT(--((LEN(" "&SUBSTITUTE(SUBSTITUTE(A1:A10,","," ")," "," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(A1:A10,","," ")," "," ")&" "," info ","")))/LEN(" info ")))

**gunny1979** · 02-27-2007, 11:11 AM

This seems to be just what I need.

Can anyone inform me how I would do it withe the text im looking up is referenced in a cell??

Thanks

Gunny

**daddylonglegs** · 02-27-2007, 04:04 PM

Which formula are you looking at?

In general you can just replace "info" with a cell reference, e.g. if cell is D2

=SUMPRODUCT(--((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,D2,"")))/LEN(D2)))

although in the last formula where "info" has a space either side you'd need a small tweak to

=SUMPRODUCT(--((LEN(" "&SUBSTITUTE(SUBSTITUTE(A1:A10,","," ")," "," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(A1:A10,","," ")," "," ")&" "," "&D2&" ","")))/LEN(D2)+2))