Data Validation on two factors at the same time

[Colin Staples]

Hello all,

An experienced Excel user, but a newbie to Mr Excel with my first question:

I'm creating a form in Excel 2007, that will be sent out to customers for completion.
Because many of these customers will have Macros disabled on their computers, I cannot use VBA.

In various cells I am using Data Validation to restrict what can be entered, so that there are no non-compliant entries when we get the form back.
e.g we cannot accept text in the telephone number field, and the post code (zip code) has a maximum length.
(We want to use DV because it will not accept their input unless it is totally compliant. With conditional formatting they can still input non-compliant data, and then choose to ignore the red flags)

In one cell (D13), I am trying to validate two factors at the same time:

* Firstly, I need to restrict the maximum length to 35 characters.
(if this was the only restriction, it would be a simple fix. But adding the second factor is where it gets complicated)

* Secondly, I need to block the use of "special characters" like £$%^&*#~@ and so on.
In fact the only characters I can accept in this cell are:

lower case a-z
upper case A-Z
numbers 0-9
space, hyphen, comma, full stop (period)​

Even accents over letters (é ó á etc) cannot be accepted.
(This is because one of the legacy systems we will ultimately input/paste this data into cannot cope with these characters)
This will most likely be achieved by creating a named range that contains only the allowed characters, and the input will be rejected if the cell contains anything not found in that named range.

How can I use Data Validation only to restrict both of these aspects in the same cell at the same time, and without using VBA?

Obviously I will be using the "Custom" option in DV, but what exactly do I put in the formula box?

Thanks in advance for your help with this.

 

[AlphaFrog]

Try this for the DV custom formula for cell D13. The list of unallowed characters is in a named range called No_Way. Adjust the list to suit (no blanks).

=AND(LEN(D13)<=35,SUMPRODUCT(ISNUMBER(FIND(No_Way,D13))*1)=0)

 

[Colin Staples]

Thanks for your help, and the reply.

Is there any way that the named range contains the list of allowed characters, rather than the list of unallowed characters?

The reason I ask is that the potential list of unallowed characters is almost endless (for example Charmap for the font Arial has 2820 characters : 141 rows x 20 columns) and if I miss anything from the unallowed list it will slip through and cause problems later.
However my allowed list is just 66 characters.

Thanks again for your help.

 

[Colin Staples]

I managed to tweak the formula so that it works as needed.

The fix was to change :
=AND(LEN(D13)<=35,SUMPRODUCT(ISNUMBER(FIND(No_Way,D13))*1)=0)
to become :
=AND(LEN(D13)<=35,SUMPRODUCT(ISNUMBER(FIND(No_Way,D13))*1)<>0)

Thanks again for your help, it's very much appreciated.