DATA VALIDATION

  • Thread starter Thread starter aditya
  • Start date Start date
A

aditya

data in column B is of 9 or 10 or 11 or 12 digit.
In every case it contains alphabet "P" whereas others are digit.
e.g. 10P060509
125P695847

how can i check this through data validation?
 
HI aditya,
data in column B is of 9 or 10 or 11 or 12 digit.
In every case it contains alphabet "P" whereas others are digit.
e.g. 10P060509
125P695847

how can i check this through data validation?
Click to expand...

Use a custom validation formula like;
=AND(LEN(B1)>8,LEN(B1)<13,ISNUMBER(FIND("P",B1)),NOT(ISERROR(VALUE(IF(LEN(B1)=FIND("P",B1),0,RIGHT(B1,LEN(B1)-FIND("P",B1)))))))
(all on one line)

This evaluates as True if the length of the string is between 9 and 12
charachters, and the string contains exactly one 'P' and all other
charachters are numeric.

Ed Ferrero
www.edferrero.com
 
Back
Top