Data validation question

[Pauljj]

I need to a cell to contain either 3 numbers or 3 numbers and a letter only.

I.E 308, 305E

In that way, if a letter appeared in any other position this would be wrong or of there was more then 4 characters and less then 3 or 2 numbers and a letter...this would be wrong.

How could I stop this ?

 

[gwkenny]

Let's say you want to do this on cell A1

Goto Cell A1

Choose Data Validation

Validation Criteria is "Custom"

Try this in the Formula and see if it works:

=IF(LEN(A1)=3,ISNUMBER(A1),IF(LEN(A1)=4,AND(ISERROR(VALUE(A1)),NOT(ISERROR(VALUE(LEFT(A1,3))))),FALSE))

Not pretty, but should do the job

 

[Pauljj]

I haven't got a clue how that works.....but it does ?

Thank you ever so much


Paul