Allow first 5 digit alpha then next 4 digit numerical and last digit alpha only

[prakashkumar]

Sir,
How to allow only first 5 digit alpha then next 4 digit numerical and last digit alpha in excel data validation

 

[Eric W]

Try:

Book1
	B
3	abcde1234f
Sheet6

Cells with Data Validation
Cell	Allow	Criteria
B3	Custom	=ISERROR(MID(B3,1,1)+0)*ISERROR(MID(B3,2,1)+0)*ISERROR(MID(B3,3,1)+0)*ISERROR(MID(B3,4,1)+0)*ISERROR(MID(B3,5,1)+0)*ISNUMBER(MID(B3,6,4)+0)*ISERROR(MID(B3,10,1)+0)*(LEN(B3)=10)

 

[prakashkumar]

wow soooper sir
tanq u

 

[Phuoc]

Or try this:

Book1
	B	C
3	abcde1janz	0
4	abcde1234z	1
Sheet1

Cell Formulas
Range	Formula
C3:C4	C3	=(COUNT(--MID(B3,{1,2,3,4,5,10},1))=0)*(COUNT(--MID(B3,{6,7,8,9},1))=4)*(LEN(B3)=10)

 

[Eric W]

Phuoc, thanks for pointing out that sometimes the "text+0" trick will incorrectly flag text as numbers, as with "1jan". That could be an issue depending on the specific data. My original formula could be remediated by taking the characters individually.

However, it's also worth noting that array constants such as {6,7,8,9} are not allowed in DV formulas.