Restrict data entry to custom format

[crosb]

Hi,

I'm trying to figure out a formula for data validation to restrict entries in a cell to a certain format... it can be a text string, doesn't have to be a date value or anything, but the format has to be:

YYYY-ABC123-123

where

yyyy is a four digit year
the dash is present
abc are any letters
123 are three numbers
the second dash is present
123 are three more numbers

Is there a way to enforce this at all? and show the ****-******-*** style to a user when they click on the cell so they know what style they have to follow?

Thanks so much in advance!!

 

[VBA Geek]

add this as validation rule, if your cell is in A1

=IF(LEN(A1)=15,AND(ISNUMBER(--LEFT(A1,4)),MID(A1,5,1)="-",SUM(--ISERROR(--MID(A1,ROW($6:$8),1)))=3,ISNUMBER(--MID(A1,9,3)),MID(A1,12,1)="-",ISNUMBER(--RIGHT(A1,3))),FALSE)

 

[crosb]

That is excellent. Thank you so much!!!

add this as validation rule, if your cell is in A1

=IF(LEN(A1)=15,AND(ISNUMBER(--LEFT(A1,4)),MID(A1,5,1)="-",SUM(--ISERROR(--MID(A1,ROW($6:$8),1)))=3,ISNUMBER(--MID(A1,9,3)),MID(A1,12,1)="-",ISNUMBER(--RIGHT(A1,3))),FALSE)