ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   alpha numeric data validation for excel (https://www.excelbanter.com/excel-worksheet-functions/211910-alpha-numeric-data-validation-excel.html)

T. Valko November 29th 08 07:33 PM

alpha numeric data validation for excel
 


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
This seems to work:

=SUM(2-LEN(SUBSTITUTE(LEFT(A2,2),CHAR(ROW(INDIRECT("65:90 "))),"")),
--(COUNT(-MID(A2,ROW(INDIRECT("3:7")),1))=5),--(LEN(A2)=7))=4

...

Or avoiding the volatile INDIRECT function but taking advantage of
ASCII encoding,

=AND(LEN(x)=7,ABS(CODE(MID(x,{1;2},1))-77.5)<13,COUNT(-MID(x,
{3;4;5;6;7},1))=5)


Nice one, Harlan.

I should've realized we could use array constants rather than
ROW(INDIRECT(...)) since it's only a few characters.


Ooops!

I can't believe that between us we didn't catch this...

Can't use array constants in a DataValidationCustom rule.


--
Biff
Microsoft Excel MVP



Harlan Grove[_2_] November 29th 08 08:02 PM

alpha numeric data validation for excel
 
"T. Valko" wrote...
....
Ooops!

I can't believe that between us we didn't catch this...

Can't use array constants in a DataValidationCustom rule.


OK, should have causght that.

=AND(ABS(CODE(A1)-77.5)<13,ABS(CODE(MID(A1,2,1))-77.5)<13,
COUNT(1/(MID(A1,3,6)=TEXT(--MID(A1,3,5),"00000"))))

Note that the 6 as 3rd arg in the 1st MID call is intentional. It
eliminates the need for a LEN test.

T. Valko November 30th 08 05:28 AM

alpha numeric data validation for excel
 





"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
Ooops!

I can't believe that between us we didn't catch this...

Can't use array constants in a DataValidationCustom rule.


OK, should have causght that.

=AND(ABS(CODE(A1)-77.5)<13,ABS(CODE(MID(A1,2,1))-77.5)<13,
COUNT(1/(MID(A1,3,6)=TEXT(--MID(A1,3,5),"00000"))))

Note that the 6 as 3rd arg in the 1st MID call is intentional. It
eliminates the need for a LEN test.


COUNT(1/(MID(A1,3,6)=TEXT(--MID(A1,3,5),"00000")))


This seems to work just as well:

MID(A1,3,6)=TEXT(--MID(A1,3,5),"00000")


--
Biff
Microsoft Excel MVP



[email protected] March 16th 16 04:04 PM

alpha numeric data validation for excel
 
Thanks a bunch, very helpful. Can you advise me on how to make changes to the validation rule if I wanted it to end with an 'Alphabet'??


On Friday, November 28, 2008 at 2:17:19 PM UTC-5, T. Valko wrote:
That allows more than 7 characters:

AB12345xxxxxx
AB1234567890

This seems to work:

=SUM(2-LEN(SUBSTITUTE(LEFT(A2,2),CHAR(ROW(INDIRECT("65:90 "))),"")),--(COUNT(-MID(A2,ROW(INDIRECT("3:7")),1))=5),--(LEN(A2)=7))=4

--
Biff
Microsoft Excel MVP


"vezerid" wrote in message
...
With the validated cell being A2:

=PRODUCT(--(CODE(MID(A2,ROW(INDIRECT("1:2")),1))=65))*PRODUC T(--(CODE
(MID(A2,ROW(INDIRECT("1:2")),1))<=90))*PRODUCT(--ISNUMBER(--MID(A2,ROW
(INDIRECT("3:7")),1)))

A bit complex but ensures that no . or E will be used in the last five
digits.

HTH
Kostis Vezerides

On Nov 28, 7:23 pm, wrote:
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?



comin March 17th 16 10:16 PM

alpha numeric data validation for excel
 
On Saturday, 29 November 2008 04:23:44 UTC+11, wrote:
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?




On Saturday, 29 November 2008 04:23:44 UTC+11, wrote:
I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.

Any ideas?




All times are GMT +1. The time now is 08:33 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com