I have a sheet where I have a reference number in Col D that is comprised of
3 letters, a space then 3, 4, 5 or 6 digits.. I need to identify duplicate
entries as people enter a duplicate reference number

Col D
ABC 1234
ABS 22221
ABR 124
ASR 554477

I have managed to find a formula within data validation that will give an
error message if a duplicate reference is entered however I need to add
something to make sure that only three letters , then a space, then numbers
can be input or that peoiple can only input letters and numbers with no
spaces or other characters.

The formula in my data validation is =COUNTIF(D$2:D$4999,D2)<=1

Any suggestions very welcome - most entries are being input correctly
however a few are including a / between text and numbers instead of space or
are adding a space after numbers so my current formula is not picking up
these as duplicates.

I'm using Excel 2000

Lilyput