Count text string in column

[JMP]

Hi All,

I am trying to create a formula that counts the number of times a specific text string appears in a column.
Within the column, the text string may appear within a larger text string within a cell.

For example, Column D has 15000 rows with various text messages.
I need to count how many times the text string "is mandatory" appears. This text string will often have text preceding or following it.

Hope someone can help!

 

[meldoc]

Hello and welcome

Try.

Excel Workbook
	D	E	F
1	btris mandatory	is mandatory	10
2	is mandatoryif
3	is mandatory
4	go,is mandatory.yop
5	ggtis mandatoryii
6	is mandatory
7	is mandatoryyu
8	is mandatory
9	yyeis mandatory
10	is mandatory
Sheet1

Excel 2007

Cell Formulas
Range	Formula
F1	=COUNTIF(D1:D10,"*"&E1&"*")

 

[JMP]

Fantastic, thanks for your help.

That was pretty easy, but I was on the right track with COUNTIF; I just couldn't use it properly!

 

[bazza45]

Hello and welcome

Try.

Sheet1

	D	E	F
1	btris mandatory	is mandatory	10
2	is mandatoryif
3	is mandatory
4	go,is mandatory.yop
5	ggtis mandatoryii
6	is mandatory
7	is mandatoryyu
8	is mandatory
9	yyeis mandatory
10	is mandatory

<thead>
</thead><tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula F1 =COUNTIF(D1:D10,"*"&E1&"*") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hi - I'm replying to this old thread, rather than starting a new one on basically the same subject

is it possible to do this to count WITHOUT a text string?
I suppose, <count all> minus <count with string> would work, but is there a specific 'countifnot'?

thanks

Barry

 

[bazza45]

Ah - doesn't like the brackets -

"I suppose, (count all) <count all="" style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">minus (countif) <count with="" string="">would work, but is there a specific 'countifnot'?"</count></count>