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?

you can limit the length of your input to 7 or 8 characters.,and probably
enforce the first two and the last two charcters as "text".would be an
interesting formula.I watch with interest!
--
paul

remove nospam for email addy!



"ChrisCasTigers" wrote:

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?

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?

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?

Good idea, Paul! It will work!
Stefi


€žpaul€� ezt Ã*rta:

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?

Thank you Paul and Steffi, you were most helpful

Cheers

You are welcome!
Stefi


€žChrisCasTigers€� ezt Ã*rta:

Thank you Paul and Steffi, you were most helpful

Cheers