Vehicle (VIN) calculation

I am trying to create a calculation in Excel for a vehicles VIN number.

The VIN is 17 characters long

3VW - FE21C - 0 - 4 - M - 000001

I need a calculation that tells me whether it is a valid VIN from the details I input.

The criteria is
1. The letters I, O and Q are not to be used anywhere in the VIN.
2. Position 9 of the VIN can be only the values 0,1,2,3,4,5,6,7,8,9 and X
3.The last five values of the VIN should always be numeric.
4. Position 10 of the VIN should always be a letter except years 2001 to 2009
5. Position 10 of the VIN cannot be U or Z.

I have tried it a few times but always seem to get stuck. If anyone has better knowledge than me then please help.

I have attached a sheet about VINS

Thanks.

M

Originally Posted by TechRetard

can you work with this where vin is in A1 and year is in B1?

Please Login or Register  to view this content.

Can you explain by Year in B1

If i just enter the VIN in A1 then I dont why I need B1. Could you kindly explain.

Thanks

Originally Posted by sinternational

Can you explain by Year in B1

If i just enter the VIN in A1 then I dont why I need B1. Could you kindly explain.

Thanks

Well the PDF you provided says that "Position 10 of the VIN should always be a letter except for model years 2001 to 2009"....how would the formula know what year your VIN is for if you don't tell it? Hence you need to supply the year so it knows whether position 10 should be a number or letter

Thanks for that.
I have checked 5 VINs and they all show true.

Appreciated.

Trying to use your formula in a spreadsheet, and it works beautifully. I'm hung up on a couple though, like this VIN: 3LG6L2GK5ER823594. It passes with your first half of the formula, but not the 2nd. I don't know if it is somehow connected to the VinCheck table, or if I need to incorporate that into my new worksheet. I'm trying to figure out what is wrong with this VIN. Thoughts?

Hello All,

I will need assistance in creating the perfect vin check excel sheet for my day to day activities.. looking at the forum and all answers to the thread are impeccable. knowing little I will oblige with the experts to help me in dealing with this.
attaching an excel for your perusal and a vin (chassis) for further process.. awaiting your response.

thank you.

Hello help here I can't seem to attach my file.

any help please

Thanks a lot LongLegs, works perfect !

Originally Posted by daddylonglegs

Just to refine this a little....

I believe that character 10 also can't be a zero......and I can shorten the part that ensures letters I, O and Q don't appear, so that part of the check can be

=AND(LEN(A2)=17,ISNUMBER(RIGHT(A2,6)+0),COUNT(SEARCH({"I","O","Q"},A2))=0,AND(MID(A2,10,1 )<>{"U","Z",0}))

also to shorten the check digit part you can use a table with the various codes and shorten formula to the following

=SUBSTITUTE(MOD(SUMPRODUCT(LOOKUP(MID(A2,ROW(INDIRECT("1:17")),1),Vincheck), Multi),11),10,"X")&""=MID(A2,9,1)

where Vincheck is a two column table that converts letters/digits to numbers and Multi is a column containing the weights.

Then if you want to you can combine these in one "mega-formula", i.e.

=AND(LEN(A2)=17,ISNUMBER(RIGHT(A2,6)+0),COUNT(SEARCH({"I","O","Q"},A2))=0,AND(MID(A2,10,1 )<>{"U","Z",0}),SUBSTITUTE(MOD(SUMPRODUCT(LOOKUP(MID(A2,ROW(INDIRECT("1:17")),1),Vincheck),Multi),11),10,"X")&""=MID(A2,9,1))

in all cases TRUE means it's valid, see attached

Sorry to reply with such a long quote, but this was an old thread so I wanted to be specific... Why would I get N/A for the Vincheck and Multi formulas when I renamed the cells as such and changed the cell from A2 to C4?? To be clear, when I enter the formula, the i get "N/A" with the explanation being "A value is not available to the formula or function"

THanks!

Hello All,

New to this forum, and boy am I happy I stumbled across all you fine folks here! I too am in need of an excel spreadsheet for VIN numbers. I'm terrible at math and wondering how I would go about making a formula in excel to get the check digit for the 9th position?! Any suggestions would be greatly appreciated.
Thank you all in advance,
Ashley

last five chars should be digits
the formula become:
=ET(NBCAR(Z2)=17;ESTNUM(DROITE(Z2;5)+0);NB(CHERCHE({"I"."O"."Q"};Z2))=0;ET(STXT(Z2;10;1 )<>{"U"."Z".0}))

I'm looking to make the same tool as yours but I don't have enough data on VIN. Can you give me the data you have, please?

Originally Posted by sinternational

I am trying to create a calculation in Excel for a vehicles VIN number.

The VIN is 17 characters long

3VW - FE21C - 0 - 4 - M - 000001

I need a calculation that tells me whether it is a valid VIN from the details I input.

The criteria is
1. The letters I, O and Q are not to be used anywhere in the VIN.
2. Position 9 of the VIN can be only the values 0,1,2,3,4,5,6,7,8,9 and X
3.The last five values of the VIN should always be numeric.
4. Position 10 of the VIN should always be a letter except years 2001 to 2009
5. Position 10 of the VIN cannot be U or Z.

I have tried it a few times but always seem to get stuck. If anyone has better knowledge than me then please help.

I have attached a sheet about VINS

Thanks.

M

I'm looking to make the same tool as yours but I don't have enough data on VIN. Can you give me the data you have, please?