Custom validation criteria!

[Kim-Anh Tran]

Hello everyone,
I would appreciate any help to set formular for custom validation tha
allow only 4 digits entry in this format: first two digits with alph
and second two digits with number.

Regards,
Kim-An

 

[Frank Kabel]

Hi
enter the following formula (for cell A1)
=(LEN(A1)=4)*(ISNUMBER(--RIGHT(A1,2)))*(NOT(ISNUMBER(--LEFT(A1,2))))

 

[Kim-Anh Tran]

Hello Frank,

I copy the code you wrote and pasted in the formula in the validatio
criteria tab. But it did not work as what I need! Maybe I did not plac
it in the right place! I would like to force this field require tw
alpha digits and follow by two number digits. Like GS50, TN11, or A05.
Please help me again! Thanks in advance!
Regards,

Kim-An

 

[Frank Kabel]

Hi
missed one check (e.g. 'T000') But besides that the formula should
work. What problem do you have?.

Try the following slightly changed formula:
=(LEN(A1)=4)*(ISNUMBER(--RIGHT(A1,2)))*(NOT(ISNUMBER(--LEFT(A1,1))))*(N
OT(ISNUMBER(--MID(A1,2,1))))

Note: your example 'A05' would not be accepted (only ONE character)

 

[Kim-Anh Tran]

Thank you so much for your quick reply. I got this message:
"The name range you speciafied can not be found"

The cell that I want this validation is Y3 and it is a merge cell
Would that be the problem?

=(LEN(Y3)=4)*(ISNUMBER(--RIGHT(Y3,2)))*(NOT(ISNUMBER(--LEFT(Y3,1))))*(N
OT(ISNUMBER(--MID(Y3,2,1)))

 

[Frank Kabel]

Hi
if you put this formula in a cell on your worksheet, do you also get an
error (formula looks o.k.). Maybe you use a diufferent delimiter (e.g.
the semicolon instead of a coma?)

 

[Kim-Anh Tran]

I went to Data_validation_setting tab,_custom criteria and paste thi
formula. And it works only in non merged cell!
Is there a way to have this work in a merged cell? If not, I just hav
to change my work sheet a little so I could have this in a non merge
cell.
Thank you so much again, Frank! And I hope that you will have a grea
day!
Regard

 

[Frank Kabel]

Hi
should also work in a merge cell (thou8gh I haven't tested it). Does it
work for a non merge cell?

 

[Kim-Anh Tran]

Yes, it works for a non merged cell. I move and delete some columm s
that I can place the formulas in where I want. It works in all no
merged cell except those merged cells. I tried several of them and tha
is the case every time.
At least I finish what I need! I know that there are a lot more thin
that I need to learn and I hope that you would teach me.
Have a good day! And thanks,
Regards,
Kim-An

 

[francis_fds]

Hi,
I need to have validation for a column in a worksheet. the dat
would be either a 10 digit number or several 10 digits number
seperated with a "/". the cells should not accept any numberstrin
lesser than 10 digits and if there are more than one 10 digit string
should accept the seperator "/" in between