Need to extract years (and other info) from strings of text in a column of cells

Grind-my-teeth: The year isn't set off by a unique delimiter.
Bang-my-head-on-the-desk: The year does not necessarily appear in the same spot in the text string from one cell to the next.

I have a supplier that provides access to new product information; we log in, generate a report of the new products being released, and then export that to a local Excel file.
This used to work pretty well; we got the data we needed, and it took relatively little massaging to prep it for our e-commerce platform.
The essential data was in discrete, separate columns: UPC, Title, Studio, Street Date, Format, Year, Number of Discs.

They've evidently changed where they are sourcing their data, or how they are compiling it.
The new format is just a nightmare. They've combined the data from a number of formerly separate columns into a single column.
Where once had:
012584875982 | Ten Seconds to Hell | Warner Bros. | 09/13/2016 | Blu-ray | 1944 | 2

We now get:
012584875982 | Ten Seconds to Hell (1944/Blu-ray/2Disc) | Warner Bros. | 09/13/2016

To make things much worse, they are not consistent with the mashed-up data. The next row below might look like:
886985473254 | Running Scared (DVD/Ws/1986) | New Video | 09/13/2016

or:
886741282145 | Viva Maria (Dvd/1965/Ws 2.35/French/English Subtitles) (2 Discs) | Lorber | 09/13/2016

I can figure out how to extract the format, and probably even the number of discs, but I haven't the faintest idea where to start on the years.
I can't possibly do an If-Then for every potential year from 1922 through 2016.
What I need is some code that would look at each cell and go, "If you see 4 numbers in a row in this string of text,then assume that's a year and extract it." I'm pretty sure that Excel won't make that distinction.
I don't think I can do it with wild-cards, because "look for '19??' or '20??' would get tripped up by something like Imax-Wonders Of The Arctic (Blu Ray W/Digital) (Ws/Hd/1920X1080/Eng)


I'm wide open to suggestions that don't involve "ask your supplier to provide better data," because that ain't gonna happen
I've got to make this work somehow; it's far too many rows to clean up manually each week.
I'm really hoping somebody out there has some insight.

Try

Please Login or Register  to view this content.

Originally Posted by scottiex

Something like this?
try it on a big set of data and see what conditions it fails. (probably some false positives when the year is part of a product id string like an imaginary code such as "DVD/1999xv12/1920/ss"

Note the thisyear and minyear variables.


obviously use like this =GetNum4D(A1)

Please Login or Register  to view this content.

Hi, Scottiex --
Thank you for your excellent bit of code. Your function works fantastically if I place =GetNum4D(A2) into my Year column and copy it down to the bottom of my data.
With your function in place, I scribbled out this quick test Sub:

Please Login or Register  to view this content.

I need to do a bit of polishing, since my source sheet will range in length from 75 to 150+ rows, but it does exactly what I need it to do! A quick Select Column ==> Copy ==> Paste Values and I'm all set!

~Gene

Originally Posted by jindon

Try

Please Login or Register  to view this content.

Thanks, jindon -- I appreciate your assistance!
If I understand your code (and there is no guarantee that I do), I don't think the array is going to work for me. My source sheets tend to be very long and will change every week, so I would have to rewrite the code for each pass.
Maybe I'm wrong?

~Gene

That's just a demonstration code.

If you can not apply the code to your data then I need to see your workbook.

Supose your data is in columnB, row 1.
Then, in Column A, row 1 try this code:

Please Login or Register  to view this content.

AND THEN PRESS CTRL+SHIFT+ENTER key combination.
This code assume that your year number to get is always in a ( ) bracket.
It will search for movie with year 19xx or 20xx, in case you have movie produced in 18xx then pls modify the code.

An improved version might be the below to start searching form the first bracket - assuming they are going to stick to this format of the date always being in the brackets.

Please Login or Register  to view this content.

Thanks to all of you for your suggestions! If I had time to burn, I'd try all of them

Since I need to get this done ASAP, I will throw out the NEXT part of my dilemma. I need to extract the number of discs from the dirty-data title.
The majority of entries will have the information in a format like this:
Ten Seconds to Hell (1944/Blu-ray/2Disc) or Running Scared (DVD/ 3 Discs /1986)

I've come up with something that I think should work, but I keep getting a Run-time error 5: Invalid procedure call or argument message.
Here's what I've got:

Please Login or Register  to view this content.

It runs through the Title column, finds the word "DISC" (it's in ALL CAPS in the original source), and theoretically should return the two characters (i.e. the number of discs) to the left, but it doesn't.

Thoughts on where I've gone south?

If you upload a sample workbook with data and your EXACT desired results, it will help more.

Asking one by one might end up with wasting time for both of us.

Originally Posted by jindon

If you upload a sample workbook with data and your EXACT desired results, it will help more.

Asking one by one might end up with wasting time for both of us.

You are certainly correct, jindon; I initially asked only about finding the year, because I thought I knew how to get the Format (which I did) and the number of discs (which, it turns out, I didn't)

I just don't understand what do you want to extract from those lines...

012584875982 | Ten Seconds to Hell (1944/Blu-ray/2Disc) | Warner Bros. | 09/13/2016
886985473254 | Running Scared (DVD/Ws/1986) | New Video | 09/13/2016
886741282145 | Viva Maria (Dvd/1965/Ws 2.35/French/English Subtitles) (2 Discs) | Lorber | 09/13/2016
Imax-Wonders Of The Arctic (Blu Ray W/Digital) (Ws/Hd/1920X1080/Eng)

So if you specify the logic, it will be very easy...

Almost there, I think.

Here's the last hangup: If I include the If IsNumeric ... Else section, the code blows up at the NumDsc = Mid(LongTitle ... line.

Please Login or Register  to view this content.

The problem is that when I add in the If Cell.Value Like "*DISC*" line and retain the If IsNumeric ... Else section, I get a Next Without For error because I've now got an extra If in there
I thought that I could just add an additional End If after the NumDsc = Mid(LongTitle line, but that produces some weird duplication in the results (Stacked IFs give me fits).

Here's what it's returning:

PSYCH-COMPLETE EIGHTH & FINAL SEASON (DVD/REPACKAGED) (3DISCS)3
COMMUNITY-SEASON 6 (DVD/WS 1.78/DOL DIG 5.1/ENG/2 DISC)2
EXPANSE-SEASON ONE (BLU RAY W/DIGITAL HD) (2DISCS)2
EXPANSE-SEASON ONE (DVD) (3DISCS)3
EXPANSE-SEASON ONE (DVD) (3DISCS)3
EXPANSE-SEASON ONE (DVD) (3DISCS)3
HEROES REBORN-EVENT SERIES (BLU RAY) (3DISCS)3
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
HEROES REBORN-EVENT SERIES (DVD) (4DISCS)4
KILLJOYS-SEASON ONE (BLU RAY W/DIGITAL HD) (2DISCS)2
KILLJOYS-SEASON ONE (DVD) (2DISCS)2
KILLJOYS-SEASON ONE (DVD) (2DISCS)2
KILLJOYS-SEASON ONE (DVD) (2DISCS)2
KILLJOYS-SEASON ONE (DVD) (2DISCS)2
KILLJOYS-SEASON ONE (DVD) (2DISCS)2
KILLJOYS-SEASON ONE (DVD) (2DISCS)2
KILLJOYS-SEASON ONE (DVD) (2DISCS)2
KILLJOYS-SEASON ONE (DVD) (2DISCS)2
KILLJOYS-SEASON ONE (DVD) (2DISCS)2
KILLJOYS-SEASON ONE (DVD) (2DISCS)2
KILLJOYS-SEASON ONE (DVD) (2DISCS)2


. . . and so on.

Still not clear to me

Please Login or Register  to view this content.

Originally Posted by jindon

I just don't understand what do you want to extract from those lines...

012584875982 | Ten Seconds to Hell (1944/Blu-ray/2Disc) | Warner Bros. | 09/13/2016
886985473254 | Running Scared (DVD/Ws/1986) | New Video | 09/13/2016
886741282145 | Viva Maria (Dvd/1965/Ws 2.35/French/English Subtitles) (2 Discs) | Lorber | 09/13/2016
Imax-Wonders Of The Arctic (Blu Ray W/Digital) (Ws/Hd/1920X1080/Eng)

So if you specify the logic, it will be very easy...

Hi, Jindon --

What I need to extract is the Year, the Format (Blu-ray, DVD, Combo), and the Number of Discs. That data will go into three separate columns (which I do know how to do)

So instead of this (I'm using the pipes to denote separate columns):
012584875982 | Ten Seconds to Hell (1944/Blu-ray/2Disc) | Warner Bros. | 09/13/2016
886985473254 | Running Scared (DVD/Ws/1986) | New Video | 09/13/2016
886741282145 | Viva Maria (Dvd/1965/Ws 2.35/French/English Subtitles) (2 Discs) | Lorber | 09/13/2016


I would have this:
012584875982 | Ten Seconds to Hell | 1944 | Blu-ray | 2 | Warner Bros. | 09/13/2016
886985473254 | Running Scared | 1986 | DVD | | New Video | 09/13/2016
886741282145 | Viva Maria | 1965 | DVD | 2 | Lorber | 09/13/2016

Does that help clarify?

Originally Posted by jindon

Still not clear to me

Please Login or Register  to view this content.

Wow -- that is slick. That's pretty much exactly what I'm looking to do!

Here's the file I'm working with.
I will try to check in on this thread throughout the weekend, but will be mostly away from my desk until Monday.
Thanks for all the help so far!

Hi, Jindon --

I think I'm going to have to wait until Monday to test this. I'm an an iMac at home, and the version of Excel I've got doesn't seem to like the code. It may be just a setting, but I don't know much about this machine (new computer, 1st Apple).
I'm getting an ActiveX error when I try to run your code. I may fiddle around with it tomorrow. Thank you again for your time and efforts. I will let you know once I get it working.

My code will not work on Mac..

Originally Posted by jindon

My code will not work on Mac..

Well, there we go. I'll be back at my comfortable old PC on Monday. Have a good weekend!

Originally Posted by scottiex

FWIW, It looks to me like they also refer to Blue-Ray as BR. (all that "BR/" at the beginning).

eg this is 2 blue-ray disks
PINEAPPLE EXPRESS (BR/2 DISC/WS 2.40 A/DD 5.1/ENG-IN-KO-CH-SUB/FR-SP-PO-TH

TELL me about it. I've never seen anything like this from a supplier. It's complete garbage, and I get to wade through it

Hi again, Jindon.

I'm back at my PC and I've just run your code on my sample spreadsheet; it appears to work like a charm.
I would love to have some insight into HOW it works, because I'll need to tweak it a bit to make it work on the full spreadsheet.
It looks like Function GetDetails matches the long titles with an array of parameters 19xx, 20xx, DVD, BLU, etc., but I'm lost after that.

Like I said already...

Don't ask one by one, it will just wasting time for both of us.
Just post a workbook with data and EXACT desired result.

Hi, Jindon --
I truly didn't mean to waste your time. I no doubt should have uploaded a full copy of my source spreadsheet instead of just the one column.
I was thinking that if I could see how your code worked on a really simple sheet, I could figure how to adapt it for future use.
Give a man a fish, he eats for a day; teach him how to fish, he eats for the rest of his life, you know?
I am attaching the full sheet.

You still need to give the exact result required.
I suggest filter the lists for the items that Jindons macro doesn't currently give a result and manually fill down those results.

this is my guess but its up to you to fill it in as you are the expert on what should be where :
all the "BR/" are blue ray
the ones that say something like "(2 DVD)" are 2 disks (and obviously 3 dvd would be 3 disks etc).
All the rest are 1 disk.
And probably you have to leave the years blank on the ones that don't mention year as there will be no way to determine it.

Change "tset" sub procedure to

Please Login or Register  to view this content.

Google with the key words like
VBA Regular Expressions meta character submatches etc.

You will see a brief idea what/how it does.

I've been away from this project for far too long. My primary job functions have forced me to mostly back-burner it. I've been chipping away at it and thought I finally had something that would do what I wanted.
I tested the macro repeatedly on my sample sheet and copies thereof until it was performing consistently. When I was confident that it worked, I exported the module to a folder on our server, then imported it to the PERSONAL.XLSB of the person who was going to be using it.
Except it didn't work on her machine. Same version of Excel, same Win 7 Pro operating system.
I should clarify; most of it work. What didn't work was scottiex's Public Function, and I don't know why. It works perfectly on my sample sheet, but when we run it with "live" data downloaded from my supplier, I get a #NAME error in the Year column. I went back to my workstation and discovered the same error.

I'm wondering if scottiex's Public Function GetNum4D(strText As String) As String only works when it's in a module that is in the active workbook, and not when it's in the PERSONAL.XLSB.
To that end, I'm posting a couple of workbooks.
The first one is the sample sheet I've been working in all along (now named Ingram Test-Bed 10-05-2016.xlsm). The module is named m2PrepIngramData. It's really long and messy, I know. Chewing Gum and Bailing Wire. I've commented it extensively, though. When I run the Macro in the Search Results sheet, it works as designed.
The second workbook, 2881001_search.xlsx, is the data exported from my supplier. In this case, I've got to run the macro from the PERSONAL.XLSB, since the module obviously isn't in the exported sheet. This is when I get the #NAME error in the year column, I think because the GetNum4D public function isn't kicking in.

So I'm stuck. Can anyone see what I'm missing here?

Hope somebody's still watching this . . .

Thanks!

Not sure.. but maybe fully 'specify' the function?
something like the below...

Please Login or Register  to view this content.

Hmm . . . maybe I'm not putting you code in the right place.
I changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

, which eliminated the #NAME error that I was getting in the Year column, but now I'm not getting anything showing up in that column.

Originally Posted by Gene@action

Hmm . . . maybe I'm not putting you code in the right place.
I changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

, which eliminated the #NAME error that I was getting in the Year column, but now I'm not getting anything showing up in that column.

dont include the "(D2)" that was just my example cell that you might be processing

'PERSONAL.XLSB'!module1.getnum4d replaces getnum4d

dont include the "(D2)" that was just my example cell that you might be processing

'PERSONAL.XLSB'!module1.getnum4d replaces getnum4d

I initially tried it without the (D2) on the idea that my GetNum4D(" & ColLttr & ct & ")" was going serve as the reference to the cell I was inserting the data into. ColLttr being the variable I defined for the column and ct being the variable I defined for each active row number. When that didn't work, I thought maybe I missed the meaning of (D2) in your code and put it back in.

I really think you may have something about specifying the function, but I'm clearly doing SOMETHING wrong. I have a feeling this has something to with the fact that the first part of my code saves an open .xls file (exported from my supplier) as an .xlsm file before running the rest of the code, but don't know how to resolve it.

Your requirement can be fulfilled with this long formula without using VBA. Put this formula in A2 and copy down.

Please Login or Register  to view this content.

May be it could be shorter. But this formula will work from 1700 to 2100.

Ow. I have answered according to your first post. I didn't notice that there remain 3 more pages. Sorry.

Originally Posted by sanram

Ow. I have answered according to your first post. I didn't notice that there remain 3 more pages. Sorry.

No worries. I've just tested it and it seems to work. Now all I need to do is figure out WHERE to cram the code into my stupidly long macro. I will give it a try tomorrow.
Thanks, sanram!

Originally Posted by scottiex

can you add my code as an add-in to the persons computer?
works fine for me as an addin

Otherwise if you are OK with one of the others solution maybe you will find that easier as I will be MIA for a few weeks soon. (or start a new thread for a wider audience !)

I actually imported the whole module, which included your Public Function code, into the end-user's PERSONAL.XLSB. That's when we discovered that what worked in my development workbook didn't seem to work in the "live" workbook we downloaded from our supplier. As it turns out, it doesn't work on MY workstation either. It only works in the development workbook, when the module is in that workbook. I'm REALLY puzzled because I thought the PERSONAL.XLSB was supposed to be applicable to whatever workbook you had open. I'd LOVE to make you code work. When you say that it works fine for you as an add-in, where are you adding it?

OK, calling it a day.
Thanks, scottiex and sanram, have a good evening!

If I understood your requirement then you can use those formulas :
For Year :

Please Login or Register  to view this content.

For Format :

Please Login or Register  to view this content.

For Number Of Discs :

Please Login or Register  to view this content.

But my formula for number of disc will work with single digit only. i.e. (1 to 9).

Originally Posted by Gene@action

OK, calling it a day.
Thanks, scottiex and sanram, have a good evening!

OK better carry on with one of the other users (like sanram) then as I'll be gone for a few weeks.

Originally Posted by scottiex

For me it was in an add-in that i add in to excel to auto load when excel opens (i don't have access to edit my personal.xlsb lol)

so I just go

file-options - addins - excel addins - browse

and assign it.

OK -- I don't know really anything about add-ins. I would put your public function into its own module, then use the above path to make it an add-in? Sorry if I'm a little dense. I think I've been at this too long today.

OK, calling it a day.
Thanks, scottiex and sanram, have a good evening!

May be I will not be able to answer you within next week, too. That's why I am uploading your sample file with my formulas.

Hope that you will find a proper solution soon.

Good day.

an addin is a file type ie .xlam so just save the file you would have called your personal .xlsb as .xlam

then put it somewhere and following the menu path I gave you can get to the screen where you browse to attach it to your excel so that it auto loads like an xlsb.

there is probably a better solution one of the other users might suggest, but like I said it worked fine for me that way so I'm guessing it should work fine for you.....

I suggest you open a new thread if neither of our last two posts get you there....