assign a number in a new cell based on certain characters in another

[asd1011]

Hi Everyone, I am new to this forum and havent done much with VBA other than in college but i remember the basics and am excited to play with it again

i am scanning QR codes into excel and want to have it automatically decode some parts of the code.

eg. 1234asdfgh is the code that i scanned.

1 23 = produced in January on the 23rd
4 = produced in mexico
a = made in 2010 (b=2011, c=2011, d=2012...)
s = produced on machine #8
dfgh = unique serial number


when i scan the code, id like it to automatically populate another cell with the year of production based on the code. it would be very useful if it could shade the cell a different color to for easy and quick recognition.

Could i get some help with this?

 

[mole999]

one way

Excel Workbook
	A	B	C	D	E
1	1234asdfgh	a	2010	a	2010
2				b	2011
3				c	2011
4				d	2011
Sheet1

 

[Rick Rothstein]

one way

Excel Workbook
	A	B	C	D	E
1	1234asdfgh	a	2010	a	2010
2				b	2011
3				c	2011
4				d	2011
Sheet1

Based on the letter and year progression of one letter per one year, I think this single formula would do...

=1945+CODE(UPPER(MID(A1,5,1)))

I would note that the OP's coding system will work only up to the year 2035 when that position in the code reaches the letter "z".

 

[asd1011]

Wow, thanks for the quick responses! Ill give these a shot and see if i can make it happen.

the date range i am working with is from about 2000-2020

any suggestions on automatically shading the cells for quick recognition of year?

Ill be scanning hundreds of these things that are mostly from the same year but about 1 in 10 will be from a different year of production so it would be helpful instead of reading each number

 

[mumps]

Just a thought: What happens if the code is 10234asdfgh where the date would correspond to October 23?

 

[Rick Rothstein]

the date range i am working with is from about 2000-2020

If the letter "a" means 2010, then what characters are used in that 5th position of the QR codes for the years 2000 to 2009?

any suggestions on automatically shading the cells for quick recognition of year?

Do you mean you want a different color for each year? If so, you will need to use VBA event code... is that okay? If yes, then what column is your scanned QR codes going in and what column is the formula for the year going in?

 

[asd1011]

Just a thought: What happens if the code is 10234asdfgh where the date would correspond to October 23?

9 is September
A is october
b is november


that confusion is my fault. i did not give all of the particulars in an attempt to keep this concise... which might just backfire. haha

 

[asd1011]

Do you mean you want a different color for each year? If so, you will need to use VBA event code... is that okay? If yes, then what column is your scanned QR codes going in and what column is the formula for the year going in?[/QUOTE]

It has been a while since i used VBA but id love to try it again

the QR code goes in column "N"
the year goes in column "L"

 

[mumps]

Can you gives us a list of example codes that would represent each month starting in January?

 

[asd1011]

Can you gives us a list of example codes that would represent each month starting in January?

1 jan
2 feb
3 mar
4 apr
5 may
6 jun
7 july
8 aug
9 sep
a oct
b nov
c dec

426HFE24KFG
426HFE24KFH
426HFE24KFI
A03IDFHJUD2
129PHFF115Q

The above a mock codes but contain all of the pertinent information