ExcelBanter - DATA VALIDATION

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - DATA VALIDATION (https://www.excelbanter.com/new-users-excel/233851-data-validation.html)

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?

Use a custom validation formula like;
=AND(LEN(B1)8,LEN(B1)<13,ISNUMBER(FIND("P",B1)),N OT(ISERROR(VALUE(IF(LEN(B1)=FIND("P",B1),0,RIGHT(B 1,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