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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany


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

Regards,
Kim-Anh


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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)


--
Regards
Frank Kabel
Frankfurt, Germany


Hello Frank,

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

Kim-Anh


---
Message posted from http://www.ExcelForum.com/

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)))

--
Message posted from http://www.ExcelForum.com

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?)

--
Regards
Frank Kabel
Frankfurt, Germany


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))))


---
Message posted from http://www.ExcelForum.com/