custom validation number and text

A

aditya

how can i write formula for custom validation for a cell like this.

first 3 digit shoulg be a number, next digit should be a text then next 6
digit should be number.

e.g. 126P151008

thanks in advance
 
G

Gary Mc

The following should return TRUE for your example in cell A1

=IF(AND(ISNUMBER(VALUE(LEFT(A1,3)))=TRUE,ISNUMBER(VALUE(MID(A1,4,1)))=FALSE,ISNUMBER(VALUE(RIGHT(A1,6)))),TRUE,FALSE)

Hope this helps,
Gary
 
Top