Highlight cells whose word count exceeds a given limit

[BlackieHamel]

I have a questionnaire being filled out by 10 respondents, and I want to limit the word count of the replies. Through some googling, I've found this formula for counting the words in a cell:

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

What I would like to do, on each of 70 rows of a spreadsheet, is to specify in Column A the max word count, and then use conditional formatting to highlight any cell whose count exceeds that limit. My first problem is to get rid of those three "A2" references, since I want to reference the current cell -- and I'm not sure how to do that.

Can you help? Thanks.

Blackie

 

[Peter_SSs]

Like this? Select B2 to the bottom right of your data and apply the CF formula shown.

Excel Workbook
	A	B	C	D
2	3	one two three	cat dog horse mouse
3	5	red blue green yellow pink orange black		blue
4	1	house	brick
5	4	chair	table	chair table plate knife fork spoon
Limit words

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =LEN(B2)-LEN(SUBSTITUTE(B2," ",""))>$A2-1	Abc

 

[jasonb75]

Try

=((LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," ",""))+1)-$A2)<0)
or
=ISNUMBER(SEARCH("|",SUBSTITUTE(TRIM(B2)," ","|",A2)))

You don't need to get rid of the references, you need them there. The reference is relative, so it always refers to the current cell.

If you want to apply the conditional formatting to C10:Z50 then change B2 to C10 (the top left cell of the range to be formatted) and $A2 to the cell that specifies the word count for that row.

 

[BlackieHamel]

Thanks, Peter! That works like a charm.

May I piggyback a question? I have a filled-in worksheet with sample answers for each row, and I'd like to calculate the average number of words in the cells of that row (this will be to come up with the limitations to go in Column A). What would that formula be?

 

[jasonb75]

Assuming that your cells to average are A1:Z1, try
=AVERAGE(INDEX(LEN(A1:Z1)-LEN(SUBSTITUTE(A1:Z1," ",""))+1,))
Note that I haven't allowed for any of the cells being empty.

 

[BlackieHamel]

Exactly what I wanted. Thanks, Jasonb75! Blackie

 

[Peter_SSs]

Thanks, Peter! That works like a charm.

You're welcome.

May I piggyback a question? I have a filled-in worksheet with sample answers for each row, and I'd like to calculate the average number of words in the cells of that row (this will be to come up with the limitations to go in Column A). What would that formula be?

Clever answer by jasonb75.