That formula allows entries like:

ABC 1E100
ABC 1.5
ABC 1


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote:

Hi,

Try to use the following formula in Data Validation Custom

=AND(CODE(LEFT(C13,1))=65,CODE(LEFT(C13,1))<=90,C ODE(MID(C13,2,1))=65,CODE(MID(C13,2,1))<=90,CODE( MID(C13,3,1))=65,CODE(MID(C13,3,1))<=90,CODE(MID( C13,4,1))=32,ISNUMBER(1*(RIGHT(C13,LEN(C13)-SEARCH("
",C13)))))

This will allow a user entry where the first three characters are
capitalised alphabets, fourth character is a space and after the space there
is a number

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Lilyput" wrote in message
...
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