Thread
:
Postcode validation
View Single Post
#
4
Posted to microsoft.public.excel.misc
paul
external usenet poster
Posts: 247
Postcode validation
wow that is GOOD stefi.Chris wach the line wrap at MID(A1,5,1)="
",ISNUMBER(VALUE(MID(A1,6,1))), should be
MID(A1,5,1)=" ",ISNUMBER(VALUE(MID(A1,6,1))),
I think.The code for space is 32 so maybe you could try
code(MID(A1,5,1))=32 .would that work stefi?
--
paul
remove nospam for email addy!
"Stefi" wrote:
This is the custom validation formula for format1:
=AND(CODE(LEFT(A1))=97,CODE(LEFT(A1))<=122,
CODE(MID(A1,2,1))=97,CODE(MID(A1,2,1))<=122,ISNUM BER(VALUE(MID(A1,3,2))),MID(A1,5,1)="
",ISNUMBER(VALUE(MID(A1,6,1))),CODE(MID(A1,7,1))= 97,CODE(MID(A1,7,1))<=122,
CODE(RIGHT(A1))=97,CODE(RIGHT(A1))<=122)
You can now create the formula for format2 by slight modification of the
above formula and insert it in the structure below:
OR(formula1,formula2)
Regards,
Stefi
€˛ChrisCasTigers€¯ ezt Ć*rta:
I'm creating a data input sheet for thousands of entries, and i want to
restrict the input cell for postcode to the following formats;
wf10 4nr = 2 letters followed by 2 numbers followed by a space
followed by 1 number then 2letters
or;
ln5 4de = 2 letters followed by 1 number followed by a space
followed by 1 number then two letters
so therefore if the data inputter types in anything other than the formats
shown above, a validation message will appear.
Can anyone help me?
Reply With Quote
paul
View Public Profile
Find all posts by paul