|
Postcode validation
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? |
Postcode validation
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? |
Postcode validation
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? |
Postcode validation
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? |
Postcode validation
Thank you Paul and Steffi, you were most helpful
Cheers |
Postcode validation
You are welcome!
Stefi €˛ChrisCasTigers€¯ ezt Ć*rta: Thank you Paul and Steffi, you were most helpful Cheers |
| All times are GMT +1. The time now is 03:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com