Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Postcode validation
Thank you Paul and Steffi, you were most helpful
Cheers |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Postcode validation
You are welcome!
Stefi €˛ChrisCasTigers€¯ ezt Ć*rta: Thank you Paul and Steffi, you were most helpful Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation with hyperlinks | Excel Worksheet Functions | |||
validation list--list depends on the selection of first list | New Users to Excel | |||
Copy workbook- Validation function | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Data Validation Window? | Excel Discussion (Misc queries) |