Word count in a cell using variable and returning error if exceeded

[JamboBob]

Hi,

I am putting together a spreadsheet for procurement purposes where the user has to limit their answer to a specified number of words (variable per question) in a cell. How can I:
1. Set the variable;
2. Count the words entered;
3. Return a word count or error message if exceeded?

I would also like to have the number of words allowed showing in the answer cell that gets deleted as they start typing but can reappear if they delete the entry, is this possible?

Any help would be greatly appreciated, thanks.

 

[reporting]

Hi there mate,
The easiest way i can think of to do this without having to code is to head to data validation in the data ribbon, in the dropdown menu choose texte length, once you do get 2 boxes in which you input the minimum and maximun text length allowed for the selected range pop up.
You have the option in last tab of the data validation window to input a text that users see whenever they exceed the max count.

Hope this helps

 

[JamboBob]

Hi there mate,
The easiest way i can think of to do this without having to code is to head to data validation in the data ribbon, in the dropdown menu choose texte length, once you do get 2 boxes in which you input the minimum and maximun text length allowed for the selected range pop up.
You have the option in last tab of the data validation window to input a text that users see whenever they exceed the max count.

Hope this helps

Hi,

My understanding is that this counts characters, not words. As this is a procurement document I need words. Happy to do some coding but just need some guidance.

Thanks

 

[Fluff]

You can do it with Data validation.
In the DV select Custom & use

=LEN(D2)-LEN(SUBSTITUTE(D2," ",""))<C2

where D2 is the cell with the DV & C2 is the max number of words.

 

[JamboBob]

You can do it with Data validation.
In the DV select Custom & use

=LEN(D2)-LEN(SUBSTITUTE(D2," ",""))<C2

where D2 is the cell with the DV & C2 is the max number of words.

Hi,

Thanks for this. How would I pass the number of characters allowed to this or do I set it up for each cell? My preference would be to have a function that looked at the number of characters allowed then did this check using this number (does that make sense?).

Thanks

 

[Fluff]

I thought you wanted the number of words, not characters?

 

[JamboBob]

I thought you wanted the number of words, not characters?

I do want the number of words.

I have a spreadsheet with a set of questions. Each question has an answer whose word count will vary (e.g., Q1 500 words, Q2 250 words, etc). I need to check the number of words is the answer does not exceed the allowable number. If I can use a function to make this repeatable that is my preferred option.

 

[JamboBob]

I do want the number of words.

I have a spreadsheet with a set of questions. Each question has an answer whose word count will vary (e.g., Q1 500 words, Q2 250 words, etc). I need to check the number of words is the answer does not exceed the allowable number. If I can use a function to make this repeatable that is my preferred option.

Example rows:

 

[Fluff]

If you just want to use a function then you can use the formula I suggested in post#4, just change the cell refs to suit.