Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ensuring Correct Vin Number is entered
When completing a job sheet we are required to enter a 17 character serial
number. For example 6J6006636DD8X2456. This would be easy enough to deal with using data validation, but the problem arises when further information is added when there are multiple units being built. For example 2 units may be entered as 6J6006636DD8X2456--2457 or 6J6006636DD8X2456 & 2457 depending on who is processing the job sheet. I want to know how I can ensure that the initial 17 character serial number length is always correct, regardless of any additional information that is added. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ensuring Correct Vin Number is entered
Try the below and feedback. I have tried in Col E
Select Colum E DataValidationCustom =LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(E1,17), " ",),"-",),"&",))=17 If this post helps click Yes --------------- Jacob Skaria "Rocko" wrote: When completing a job sheet we are required to enter a 17 character serial number. For example 6J6006636DD8X2456. This would be easy enough to deal with using data validation, but the problem arises when further information is added when there are multiple units being built. For example 2 units may be entered as 6J6006636DD8X2456--2457 or 6J6006636DD8X2456 & 2457 depending on who is processing the job sheet. I want to know how I can ensure that the initial 17 character serial number length is always correct, regardless of any additional information that is added. Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ensuring Correct Vin Number is entered
Thank you Jacob. Works great.
"Jacob Skaria" wrote: Try the below and feedback. I have tried in Col E Select Colum E DataValidationCustom =LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(E1,17), " ",),"-",),"&",))=17 If this post helps click Yes --------------- Jacob Skaria "Rocko" wrote: When completing a job sheet we are required to enter a 17 character serial number. For example 6J6006636DD8X2456. This would be easy enough to deal with using data validation, but the problem arises when further information is added when there are multiple units being built. For example 2 units may be entered as 6J6006636DD8X2456--2457 or 6J6006636DD8X2456 & 2457 depending on who is processing the job sheet. I want to know how I can ensure that the initial 17 character serial number length is always correct, regardless of any additional information that is added. Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to correct the decimal number to the nearest even number | Excel Worksheet Functions | |||
Ensuring input is rounded up to nearest 10 | Excel Discussion (Misc queries) | |||
Ensuring 3 cells do NOT match | Excel Discussion (Misc queries) | |||
Select correct worksheet based on data entered into a cell | Excel Worksheet Functions | |||
Ensuring deleted data cannot be recovered | Excel Discussion (Misc queries) |