Data validation question

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,011
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 ?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

gwkenny

Well-known Member
Joined
Aug 13, 2002
Messages
565
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

Well-known Member
Joined
Mar 28, 2004
Messages
2,011
I haven't got a clue how that works.....but it does ?

Thank you ever so much


Paul
 

Watch MrExcel Video

Forum statistics

Threads
1,118,228
Messages
5,571,000
Members
412,353
Latest member
SofiaV
Top