Data Validation - Set Format

[tlc53]

Hi there,

I currently have a data validation code which only allows the following formats: ### or ###/###

I would like to amend the code so it only accepts the following format: 620/###

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

Can someone help me with this please?

Thank you!

 

[jtakw]

Hi,

Try this:

=AND(LEFT(F14,4)="620/",ISNUMBER(RIGHT(F14,3)+0),LEN(F14)=7)

 

[tlc53]

Cool, thank you! That works

 

[jtakw]

You're welcome, thanks for the feedback.