Data Validation - two formats only

[tlc53]

Hi there,

I would like to have a column which only accepts the following two formats to be entered;

### or ###/###

Example;
499 or 499/001

I'm guessing I can do this under Data Validation??

Thanks for your time!

 

[JoeMo]

Data Validation restricts the type of data entered in a cell, not the format of the data.

Assuming ### or ###/### where the #'s are digits and do not all have to be the same is what you want, this DV custom formula seems to work. In this case it's applied to range P4:P10, change the range to suit your application.

=OR(AND(ISNUMBER(P4),LEN(P4)=3),AND(ISNUMBER(LEFT(P4,3)+0),ISNUMBER(RIGHT(P4,3)+0),ISNUMBER(SEARCH("/",P4)),LEN(P4)=7))

 

[tlc53]

Thank you!! That is very clever and has done the trick

 

[Rick Rothstein]

For the same range JoeMo assumed, I am pretty sure this shorter data validation formula will also work...

=((LEN(P4)=3)+(LEN(P4)=7))*ISNUMBER(-REPLACE(P4,4,1,""))*(MID(P4&"/",4,1)="/")