Data Validation: Limit cell entry to [A-Z] [a-z]

[shawthingz]

Hi All,

I'm trying to solve a frustating problem - hope someone can help out there!

I'm currently using custom validation in various cells in a worksheet using the istext() function to restrict the characters a User can enter.

However, I've now been asked to restrict all characters entered to [A-Z] [a-z] only & I'm currently struggling to come up with a solution.

Some examples that would currently be accepted are:

shawth1ngz
shawthingz1
sh@wthingz


However I've now been asked to change the logic to fail them, so that only shawthingz (containing alphabetic chars) would be accepted, as the input eventually ends up being uploaded to a database (& various characters in some of the fields have been causing some problems).

Does anyone out there have any ideas on how to do this using cell validation?

Thanks for any help / guidance

shawthingz

 

[Marcelo Branco]

Hi,

Maybe this can help

Create a string constant like

Formula > Names Manager > New

Name: Letters
Scope:Workbook
Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Select the range (cell) to be validated (assuming A1) and

Data > Data Validation > Custom
enter this formula

=ISERROR(MATCH(FALSE,ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),Letters)),0))

Then you can copy + paste-special-validation to any cell or range

M.

 

[mikerickson]

You could do this,
Select A1 and Define this name
Name: oneToLen
RefersTo: =ROW(INDEX(!A:A, 1, 1):INDEX(!A:A, LEN(!$A1), 1))

Then set validation with this formula
=(0 = SUMPRODUCT(--(EXACT(LOWER(MID(A1,oneToLen,1)),UPPER(MID(A1,oneToLen,1))))))

 

[T. Valko]

Hi All,

I'm trying to solve a frustating problem - hope someone can help out there!

I'm currently using custom validation in various cells in a worksheet using the istext() function to restrict the characters a User can enter.

However, I've now been asked to restrict all characters entered to [A-Z] [a-z] only & I'm currently struggling to come up with a solution.

Some examples that would currently be accepted are:

shawth1ngz
shawthingz1
sh@wthingz


However I've now been asked to change the logic to fail them, so that only shawthingz (containing alphabetic chars) would be accepted, as the input eventually ends up being uploaded to a database (& various characters in some of the fields have been causing some problems).

Does anyone out there have any ideas on how to do this using cell validation?

Thanks for any help / guidance

shawthingz

Create this defined name:

Name: Letters
Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Note that the case is important!

To validate cell A2...

Select the cell
Data Validation
Allow: Custom
Formula:

=COUNT(FIND(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1),Letters))=LEN(A2)

Uncheck: Ignore blank
Ok out

Note that if you test that formula on the worksheet it must be array entered.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[T. Valko]

Hi,

Maybe this can help

Create a string constant like

Formula > Names Manager > New

Name: Letters
Scope:Workbook
Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Select the range (cell) to be validated (assuming A1) and

Data > Data Validation > Custom
enter this formula

=ISERROR(MATCH(FALSE,ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),Letters)),0))

Then you can copy + paste-special-validation to any cell or range

M.

Did you actually test that as data validation or did you just test it on the worksheet?

 

[Marcelo Branco]

Did you actually test that as data validation or did you just test it on the worksheet?

Biff,

I tested it as data validation

M.

 

[T. Valko]

Biff,

I tested it as data validation

M.

Following your instructions, that formula doesn't work for me in either Excel 2007 or Excel 2002.

There is a "bug" in data validation that if the formula contains a defined name the validation doesn't work as expected.

For example, using that formula as the validation rule the cell will accept entries like:

123
$$$
[space][space]K

If you uncheck Ignore Blank then that "fixes" the "bug".

However, even with Ignore blank unchecked that formula will still allow entires that contain wildcard characters like:

AB*
AA?
???
*****
*A*

 

[Marcelo Branco]

Biff,

Yes...i had forgotten to say uncheck Ignore Blanks

And i had not tested with wild characters....My bad

How did you solve this issue?

M.

 

[pgc01]

Hi

Remark: One thing I like in Mike's formula is that it is not restricted to English words, as it also accepts letter with diacritics, like á, ü, etc.

Another similar option:

=COUNT(FIND(LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),UPPER(A1)))=0

 

[Marcelo Branco]

Biff,

Tks for the correction

I think this works

=ISERROR(MATCH(FALSE,ISNUMBER(FIND(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1),Letters)),0))

(didnt know that the use of UPPER is so important. Tks again)

M.