Enter specific formulated data - numbers & letters with dashes

[dillpickle]

I am working on a shared spreadsheet for tracking and I need users to ONLY be able to enter a value that is LLNNNNN-NN (letters and numbers) and in another column (different data) LNNNNNNN-NN. Both are combos of letters and numbers but I need users to only be able to enter it in these ways.

For Example > Column D > AA12345-01
For Example > Column E > K1089383-03

We have asked numerous "experts" and nobody can seem to help us. HALP!

 

[etaf]

how about
=AND(LEN(D2)=10,MID(D2,8,1)="-")

to excled symbols
=AND(LEN(D2)=10,MID(D2,8,1)="-",ISNUMBER(SUMPRODUCT(SEARCH(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1),"-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

under data validation

for column D

it will allow any value to be used symbols etc - so that can be updated to something like
=ISNUMBER(SUMPRODUCT(SEARCH(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1),"-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))
which allows - - but the AND only allows - in 1 place
i'll see and may add a new post
=AND(LEN(D2)=10,MID(D2,8,1)="-",ISNUMBER(SUMPRODUCT(SEARCH(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1),"-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

But will allow a 2nd - anywhere - will work on that as well

but will data validation work

 

[etaf]

ok, had another play
=AND(LEN(D2)=10,COUNT(FIND(MID(D2,SEQUENCE(LEN(D2)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))=9,MID(D2,8,1)="-")

Book4
	A	B	C	D	E	F	G	H	I
1
2				AB12345-01		TRUE	TRUE
3
4						TRUE
5						=AND(LEN(D2)=10,COUNT(FIND(MID(D2,SEQUENCE(LEN(D2)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))=9,MID(D2,8,1)="-")
6		For Example > Column D > AA12345-01
7		For Example > Column E > K1089383-03
8
Sheet1

Cell Formulas
Range	Formula
F2	F2	=AND(LEN(D2)=10,MID(D2,8,1)="-",ISNUMBER(SUMPRODUCT(SEARCH(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1),"-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"))))
G2	G2	=ISNUMBER(SUMPRODUCT(SEARCH(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1),"-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))
F4	F4	=AND(LEN(D2)=10,COUNT(FIND(MID(D2,SEQUENCE(LEN(D2)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))=9,MID(D2,8,1)="-")

Cells with Data Validation
Cell	Allow	Criteria
D2	Custom	=AND(LEN(D2)=10,COUNT(FIND(MID(D2,SEQUENCE(LEN(D2)),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"))=9,MID(D2,8,1)="-")