How to get a warning message when less than 16 words are filled in

[Guest]

I am looking for a way in Excel 2000 to have a warning message when someone
fills in less than 16 words in a cell. Is this possible?

Earlier I received the following advice:
Use this formula in Data > Validation > Settings > Custom > Formulas:
IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1<15,"Minimum of 15 words","")
In which A1 is the cell that should contain more than 15 words.

However, this does not seem to work. No matter what number of words are
filled in in cell A1, there always is an error message.
Does anyone know of a way to do this?

 

[Don Guillett]

try this
=if(SUMPRODUCT((a1<>"")*(LEN(TRIM(a1))+1-LEN(SUBSTITUTE(a1,"
",""))))<15,"mmm","")

 

[Roger Govier]

Hi Martin

Since you want the error message when there is less than 16 words, the
test needs to be >15 not less than 15.
Also, all you need is
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1<>5
The message "minimum 15 words" should appear on the Error tab in Fata
Validation.

 

[Debra Dalgleish]

With cell A1 selected, use the following formula in the Data Validation
Custom Formula box:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))>=15

Users will see your warning message if there are fewer than 15 space
characters between the words. The Trim function will remove space
characters at the start or end of the text, so they won't be counted.

 

[Guest]

Thanks for all the advice! Unfortunately, I still get the warning message no
matter the number of words I enter in the cell.
Any ideas what I might be doing wrong? Does the formula work when you use it?

By the way, the formula I enter is LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),"
",""))>=15
So, I enter the formula without the '=' in front of it (otherwise it gives
an error message since the program already assumes that you are entering a
formula and therefore does not require you to enter the '=')

 

[Debra Dalgleish]

The formula should start with an equal sign. What error message do you
get when you try to add one?

There are other examples of custom formulas here, that may help you see
how to input them:

http://www.contextures.com/xlDataVal07.html

 

[Guest]

The error message I get reads: “The formula you typed contains an error. …â€
I also get this error when I just type in the formula in a cell instead of
in the Validation box.
A specific part (the part between *** ***) of the formula is then selected,
suggesting that the error is in that part of the formula:
=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)***,"*** ",""))>=15

I have also tried to cut the formula down into separate parts. The part that
gives the previous error message:
=substitute(trim(a1)," ","")

 

[Debra Dalgleish]

Perhaps your computer is set up to use semi-colons, instead of commas,
as separators in formulas. Try this instead:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);" ";""))>=15

 

[Guest]

Thanks!!!! This indeed did the trick!

Perhaps your computer is set up to use semi-colons, instead of commas,
as separators in formulas. Try this instead:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);" ";""))>=15

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.