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.

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.

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.