Hi,
I need to extract year & week from list of serial numbers starting with S8H, SB, SCN, SE, SEN, SLN. After the mentioned Series..... Next 2 digits are mfg. year and next 2 digits are week of the year. e.g. S8H1323F15959.... S8H is series, 13 = year (2013) , 23=week of year (June). Please help with formula to extract year & week from uneven series codes.
File attached.
Thanks.
Not sure where you want to put this, bit this will pull the year...
=2000+MID(A4,IF(ISNUMBER(--MID(A4,3,1)),3,4),2)
and this will pull the week...
=--MID(A4,IF(ISNUMBER(--MID(A4,3,1)),5,6),2)
both copied down as needed
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Try this:
Formula:
Please Login or Register to view this content.
Try
B4=MID(A4,4,4)+0
C4=CHOOSE(MONTH(DATE("20"&LEFT(MID(A4,4,4)+0,2)+0,1,(RIGHT(MID(A4,4,4)+0,2)+0)*7-2)-WEEKDAY(DATE((RIGHT(MID(A4,4,4)+0,2)+0),1,3))),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"."&LEFT(MID(A4,4,4)+0,2)+0
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
zbor, nice one
Hi friends, thanks for your quick help but its half way done. My series are uneven i.e. S8H, SB2, SCN, SE2, SEN, SLN, SUA, S7A, E2, 7A, B2, PD, 5D, UD, S5D, BA, G1, GJ and so on..... so can we store these series somewhere in the worksheet or other worksheet in same file and link the series codes and then extract the year & date codes. Would appreciate your final resolution to this query. Thanks again for your time and help. Regards, Anil
SB21406001998
From the above which part is Year and which part is Week Number
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
for S8H there are 3 Year/Wk - 1323,1339 and 1347 in this case how to show the result
Dear Sir,
Against below mentioned series, next 2 digit is year and other next 2 digit is week of year:
S8H, SB2, SCN, SE2, SEN, SLN, SUA, S7A, E2, 7A, B2, PD, 5D, UD, S5D, BA, G1, GJ
e.g.
SB21406001998 : Series starts with SB2 & Digit 1406 where 14 is year 2014 and 06 is week of year 2014 (Feb)
S8H1339F10034 : Series starts with S8H & Digit 1339 where 13 is year 2013 and 39 is week of year 2013 (Sep)
SCN1310KD1823: Series starts with SCN & Digit 1310 where 13 is year 2013 and 10 is week of year 2013 (Mar)
SE21333000038 : Series starts with SE2 & Digit 1333 where 13 is year 2013 and 33 is week of year 2013 (Aug)
SEN1307GD1844: Series starts with SEN & Digit 1307 where 13 is year 2013 and 07 is week of year 2013 (Feb)
.... and similalry my other series begins with codes SLN, SUA, S7A, E2, 7A, B2, PD, 5D, UD, S5D, BA, G1, GJ .... rest 4 digits followed are yyww (year & week of that year).
Hope I have clarified my query correctly. Thanks for your time.
Please check the attached file.
=DATE(2000+MID(A4,4,2),1,1)+(MID(A4,6,2)-1)*7-WEEKDAY(DATE(2000+MID(A4,4,2),1,1),2)+1
Try this and copy towards down
MID command will work only where first 3 characters are fixed but in some series it starts after 2 digits.
How you will define serial nos. G11449198768 and GJ1352157435 where MID starts from 3 and not 4.
how can you bifurcate which series starts starts after 2 digits and which series starts starts after 3 digits
Hi friends, Anyone can give solution to my query.
if you provide the digit number at which the year will start in one column then you will get the solution, otherwise it is difficult to say at which position the year will starts
OK Then try
B4C4
Please Login or Register to view this content.AND COPY TOWARDS DOWNPlease Login or Register to view this content.
Is there any method that we keep the series in vlookup and then co-relate the corresponding data.
Enter in H4 and fill down. Format as General
Formula:Please Login or Register to view this content.
Enter in I4 and fill down Format as mmm-yy
Formula:Please Login or Register to view this content.
S8H SB SCN SE SEN SLN
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Re: Extract Year & Week from Product uneven Serial Number
Hi, Though I am not fully satisfied with the answers but wherever MID is coming 3 instead of 4, I am doing the changes manually.
Infact, I was looking for an automatic solution without doing any manual changes. However, thanks to you all for spending the time in studying my query. Regards,
I suspect that you have spaces in front of the data in the data in column A and maybe spaces after.
If that is the case, this modification to the formula by zbor should fix the problem.
Enter in B4 and fill down.
Formula:Please Login or Register to view this content.
This should also fix problems in the small table G4:I9 that were caused by spaces if that was indeed the problem.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks