Help with a formula to extract dates from SKUs

**rickyanalog** · 06-13-2016, 09:13 PM

I am stuck with the task of trying to pull dates from inventory SKUs for over 150k items. There is no way I could ever do this manually one SKU at a time. I was hoping there was a formula I could apply to all of them that would extract the date from the SKUs. Our SKUs all follow a simple format. An example would be NFL-2016-MAY-14-VG-001. The prefix NFL denotes where the item came from. The 2016-MAY-14 is the date the item was listed. This is the piece of data I need to extract as a date into another column of the spreadsheet, preferably in a normal date format. The VG is the condition of the item, and the 001 is simply a number appended to the end to separate it from the next item in the batch of inventory which would have most likely been NFL-2016-MAY-14-VG-002. The hyphens simply separate out everything.

Is there any formula that will be able to look at that cell and say, the date needed to be extracted will be 05/14/2016? This is the hardest excel problem I have ever tried to solve, but obviously my excel skills are limited. Any help on this problem would be EXTREMELY appreciated.

**alansidman** · 06-13-2016, 09:49 PM

A couple of Assumptions.
1. SKU is in Cell A1
2. The prefix (NFL or other) is always 3 characters long
3. The Date format in the SKU is always 11 characters long (YYYY-MMM-DD)
4. There are hyphens in the SKU
In B1 type =(MID(A1,5,11)) and copy it down.

**FlameRetired** · 06-13-2016, 09:49 PM

I cannot tell from your profile if this will work, but starting in Excel 2013 there is a new Flash Fill feature.

With your SKUs starting in say A1 go to B1 and type in just the part you want to extract from that one sample. Then click Data and Flash Fill. A fly-out icon will prompt for Accept / Undo. This often solves these problems very quickly.

**Nebu** · 06-13-2016, 09:59 PM

Hi:

One way of doing this, if your string format is going to be always same as you have given in your OP

Formula:

=MID(A1,FIND("-",A1)+1,FIND(CHAR(135),SUBSTITUTE(A1,"-",CHAR(135),3))-2)

Thanks

**rcm** · 06-13-2016, 10:02 PM

The following code will extract the date assumming the following:

1. The SKU are in a worksheet named "sheet1"
2. The SKU is in column "B" and its format is allways XXX-YEAR-MTH-DY-....
3. The date will be placed in the next cell column "C"

Sub datefield()

Set sh1 = Sheets("sheet1")

r = 2
While sh1.Cells(r, 2) <> ""
   st = sh1.Cells(r, 2)
   yr = Mid(st, 5, 4)
   mn = Mid(st, 10, 3)
   dy = Mid(st, 14, 2)
   sh1.Cells(r, 3) = dy & "-" & mn & "-" & yr
    r = r + 1
Wend


End Sub

**mongoose36** · 06-13-2016, 10:05 PM

Assumptions:
1. Text in A1
2. Hyphens in the text
3. Only one hyphen between first part and year
4. One hyphen between the year and month and between the month and day
5. Month is always Characters and is the following (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
6. Date is always 11 characters.
Copy and paste in B2-- it converts the text to an actual date that you can display however you want.

Formula:

=DATE(LEFT(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),4),IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="JAN",1,IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="FEB",2,IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="MAR",3,IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="APR",4,IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="MAY",5,IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="JUN",6,IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="JUL",7,IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="AUG",8,IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="SEP",9,IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="OCT",10,IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="NOV",11,IF(MID(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1,3)="DEC",12)))))))))))),RIGHT(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),LEN(RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))-FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)),FIND("-",RIGHT(LEFT(A1,FIND("-",A1)+11),LEN(LEFT(A1,FIND("-",A1)+11))-FIND("-",A1)))+1)))

**rickyanalog** · 06-13-2016, 10:57 PM

I am completely amazed at this community's willingness to help. I greatly appreciate everyone's input. Mongoose36's formula looks like it will work. The only problem is that we use MLA abbreviation standards for the months, so for example June would appear as JUNE in the SKU. I will probably just run a find and replace on all the 4 character months though and then the formula will most likely work just fine.

Some of the other suggestions would have worked great if it weren't for 1 or 2 small issues, one of them being that not every prefix is 3 characters.

**protonLeah** · 06-14-2016, 12:30 AM

Solⁿ#4 MODIFIED:

=MID(A1,FIND("-",A1)+1,FIND(CHAR(135),SUBSTITUTE(A1,"-",CHAR(135),4))-FIND("-",A1)-1)

**FlameRetired** · 06-14-2016, 05:36 PM

Another way.

Formula:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"-",REPT(" ",256),4),"-",REPT(" ",256),1),256,256))

**FlameRetired** · 06-14-2016, 06:01 PM

There is probably a shorter way to do this, and using a helper cell might be a wiser method.

Is there any formula that will be able to look at that cell and say, the date needed to be extracted will be 05/14/2016?

This formula does that. It converts the string to numbers. If you are not aware of it dates are numbers which can be formatted as dates. This will return 42504. When formatted for date m/d/yyyy is 5/14/2016.

Formula:

=LOOKUP(25^25,--LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"-",REPT(" ",256),3),"-",REPT(" ",256),2),256,256)),MONTH(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"-",REPT(" ",256),3),"-",REPT(" ",256),2),256,256))&0)),"-",REPT(" ",256),1),256)),ROW($1:$50)))

Row\Col	A	B
1	NFL-2016-MAY-14-VG-001	5/14/2016

This SUBSTITUTES the month name with the month number. The MONTH(<monthname>&0) part converts the name to a number.

**FlameRetired** · 06-14-2016, 06:08 PM

Double posted.

**AlKey** · 06-14-2016, 06:21 PM

If all you need is the date without conversion to the real date

Formula:

=MID(A1,SEARCH("-????-???-??-",A1)+1,11)

v	A	B
1	NFL-2016-MAY-14-VG-001	2016-MAY-14
2	LLLL-DD-2015-JAN-25-VVVVV-25	2015-JAN-25

If you need it converted to the real date

Formula:

=(RIGHT(MID(A1,SEARCH("-????-???-??-",A1)+1,11),2)&MID(MID(A1,SEARCH("-????-???-??-",A1)+1,11),6,3)&MID(MID(A1,SEARCH("-????-???-??-",A1)+1,11),1,4))+0

v	A	B
1	NFL-2016-MAY-14-VG-001	5/14/2016
2	LLLL-DD-2015-JAN-25-VVVVV-25	1/25/2015

**FlameRetired** · 06-14-2016, 06:29 PM

@ AlKey

Thanks for another lesson.

I tried every way I could think of to get the date value. That one never occurred to me.

Dave

**FlameRetired** · 06-14-2016, 08:17 PM

rickyanalog,

Regarding your PM and helper cell try these. They work no matter the length of month text or prefix. If the locations of the "-" characters change this will not work.

Edited Changed my sample SKUs.

Row\Col	A	B	C	D
1	SKUs	Helper	Dates
2	WNFL-2016-MAY-14-VG-001	MAY	5/14/2016	In B2 and filled down =TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"-",REPT(" ",256),3),"-",REPT(" ",256),2),256,256))
3	NL-2016-JUNE-14-VG-002	JUNE	6/14/2016	In C2 and filled down =SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"-",REPT(" ",256),4),"-",REPT(" ",256),1),256,256)),B2,MONTH(B2&0))+0
4	RSTUVWXYZ-2016-AUGUST-14-VG-003	AUGUST	8/14/2016	Format for date.
5	99-2016-MAY-7-VG-004	MAY	5/7/2016
6	2-2016-SEP-1-VG-005	SEP	9/1/2016
7	NFL-2016-DECEMBER-14-VG-006	DECEMBER	12/14/2016
8	NFL-2016-JUN-14-VG-007	JUN	6/14/2016

Help with a formula to extract dates from SKUs

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Re: Help with a formula to extract dates from SKUs

Thread Information

Users Browsing this Thread

Similar Threads

skus in columm a for each sale, qty. of skus sold, each sale in co

skus in columm a for each sale, qty. of skus sold, each sale in co-Need to sort

[SOLVED] skus in columm a for each sale, qty. of skus sold, each sale in co

Bookmarks

Bookmarks

Posting Permissions