# Thread: Customized Data Validation (Excel 2000)

1. ## Customized Data Validation (Excel 2000)

Is it possible to add customized data input validation ? For example, user can only input data in the cell in the following format: "three letters + four digits + two letters" (i.e. BBC3423AB, YZE7431TC ...)

2. ## Re: Customized Data Validation (Excel 2000)

=(MID(a1,1,1)>="A")*(MID(a1,1,1)<="Z")*(MID(a1,2,1 )>="A")*(MID(a1,2,1)<="Z")*(MID(a1,3,1)>="A")*(MID (a1,3,1)<="Z")*(ISNUMBER(VALUE(MID(A1,4,4))))*(MID (a1,8,1)>="A")*(MID(a1,8,1)<="Z")*(MID(a1,9,1)>="A ")*(MID(a1,9,1)<="Z")

The formula must be <256 char so you are near the limits so not too many more conditions can be added

Steve

3. ## Re: Customized Data Validation (Excel 2000)

Thanks ! This works well. However, what I actually need is "AAAAAAA00" (seven characters plus two numbers). Anyway to get around the 256 character limit ?

4. ## Re: Customized Data Validation (Excel 2000)

Why didn't you say so in your original question? The solution is to put the validation formula in a cell (which may be in a hidden column).

Say that you want to validate cell A1. Put the following formula into cell B1:

=(MID(A1,1,1)>="A")*(MID(A1,1,1)<="Z")*(MID(A1,2,1 )>="A")*(MID(A1,2,1)<="Z")*(MID(A1,3,1)>="A")*(MID (A1,3,1)<="Z")*(ISNUMBER(VALUE(MID(A1,8,2))))*(MID (a1,4,1)>="A")*(MID(A1,4,1)<="Z")*(MID(A1,5,1)>="A ")*(MID(A1,5,1)<="Z")*(MID(A1,6,1)>="A")*(MID(A1,6 ,1)<="Z")*(MID(A1,7,1)>="A")*(MID(A1,7,1)<="Z")*(I SNUMBER(VALUE(MID(A1,8,2))))

and use =B1 as custom validation formula. You could also create a user-defined function:

Function IsValidEntry(strVal) As Boolean
IsValidEntry = (strVal Like "[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z]##")
End Function

and enter the formula =IsValidEntry(A1) in cell B1, and again use =B1 as custom validation formula.

5. ## Re: Customized Data Validation (Excel 2000)

You got around the issue of not being able to use a custom function in datavalidation with an intermediate value. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Much nicer than mine, if you don't mind the intermediate.

Steve

6. ## Re: Customized Data Validation (Excel 2000)

I didn't think of it myself - see MSKB article XL97: Cannot Use Custom Function with Data Validation.

7. ## Re: Customized Data Validation (Excel 2000)

Great ! Many thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•