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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Martin - 02 Dec 2006 13:56 GMT

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?

Reply to this Message

Don Guillett - 02 Dec 2006 14:23 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>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?
[quoted text clipped - 7 lines]
> filled in in cell A1, there always is an error message.
> Does anyone know of a way to do this?

Reply to this Message

Roger Govier - 02 Dec 2006 17:32 GMT

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.

Signature

Regards

Roger Govier

>I am looking for a way in Excel 2000 to have a warning message when
>someone
[quoted text clipped - 9 lines]
> filled in in cell A1, there always is an error message.
> Does anyone know of a way to do this?

Reply to this Message

Debra Dalgleish - 02 Dec 2006 17:49 GMT

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.

> 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?
[quoted text clipped - 7 lines]
> filled in in cell A1, there always is an error message.
> Does anyone know of a way to do this?

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Martin - 02 Dec 2006 19:36 GMT

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 '=')

> With cell A1 selected, use the following formula in the Data Validation
> Custom Formula box:
[quoted text clipped - 16 lines]
> > filled in in cell A1, there always is an error message.
> > Does anyone know of a way to do this?

Reply to this Message

Debra Dalgleish - 02 Dec 2006 19:58 GMT

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

> Thanks for all the advice! Unfortunately, I still get the warning message no
> matter the number of words I enter in the cell.
[quoted text clipped - 26 lines]
>>>filled in in cell A1, there always is an error message.
>>>Does anyone know of a way to do this?

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Rate this thread: