Formula to lookup the last digit and second to last digit in VIN number

%3CLINGO-SUB%20id%3D%22lingo-sub-1823317%22%20slang%3D%22en-US%22%3EFormula%20to%20lookup%20the%20last%20digit%20and%20second%20to%20last%20digit%20in%20VIN%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1823317%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20fleet%20of%20600%20pieces%20of%20equipment.%20I%20need%20to%20perform%20a%20look%20up%20that%20looks%20at%20the%20last%20digit%20in%20the%20VIN%20and%20then%20another%20lookup%20for%20the%202nd%20to%20last%20digit.%20For%20example%3A%26nbsp%3B%20VIN%20%23%20123456789%20-%20I%20need%20to%20have%20a%20formula%20to%20determine%20if%20last%20digit%20is%20an%20even%20or%20odd%20and%20then%20another%20formula%20to%20determine%20if%20second%20to%20last%20digit%20is%20even%20or%20odd.%26nbsp%3B%20Can%20anyone%20help%3F%26nbsp%3B%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1823317%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1823340%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20lookup%20the%20last%20digit%20and%20second%20to%20last%20digit%20in%20VIN%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1823340%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847648%22%20target%3D%22_blank%22%3E%40clambert70%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20have%20a%20VIN%20in%20D2.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20E2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DISEVEN(E2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20will%20return%20TRUE%20if%20the%20last%20digit%20is%20even%2C%20FALSE%20if%20it%20is%20odd.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20F2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DISEVEN(QUOTIENT(D3%2C10))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20will%20return%20TRUE%20if%20the%20next-to-last%20digit%20is%20even%2C%20FALSE%20if%20it%20is%20odd.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have a fleet of 600 pieces of equipment. I need to perform a look up that looks at the last digit in the VIN and then another lookup for the 2nd to last digit. For example:  VIN # 123456789 - I need to have a formula to determine if last digit is an even or odd and then another formula to determine if second to last digit is even or odd.  Can anyone help?  Thank you

2 Replies
Highlighted

@clambert70 

Let's say you have a VIN in D2.

Enter the following formula in E2:

 

=ISEVEN(E2)

 

This will return TRUE if the last digit is even, FALSE if it is odd.

 

Enter the following formula in F2:

 

=ISEVEN(QUOTIENT(D3,10))

 

This will return TRUE if the next-to-last digit is even, FALSE if it is odd.

Highlighted

@clambert70 

 

Assuming VINs are entered as text, something like this should meet your need. There are always multiple ways to use Excel to resolve situations, however, and I'm sure this is no exception. The heart of the matter, though, is in realizing you can identify the last and next to last, using the RIGHT and LEFT functions, and then the ISODD and ISEVEN functions answer the question of the nature of those digits. How you choose to put it all together, whether or not you make use of IF or IFS---all that's up to you.

mathetes_0-1603812332939.png