The most difficult "Extract Date from Text String" request I've seen..

[drakepertuit]

The difficulty in this request is the incredible variance in the text strings. There are over 1600 lines, each include a date of some sort. Many are standard and easy to extract such as:

833_RATE_FMG_AETNA HMO (A-5)_01.01.03
3007_BASE_SFV_MVIEW (BASE A)_01.01.04

However the difficulty is that others are listed as variation of these:

6675_RATE_APS Fee Schedules_20090201
833_RATE_FMF_AETNA_HMO_(A-06) 1.1.04
5518_LANG_PMI_BSHLD_(1st Amend) 2 1 17
3102_BASE_Trinity_UHC FPA_03 01 2011
3370_RATE_Coventry SJHC contract Amd Mar 01 2012
6673_RATE_Anthem Blue Cross & Facey Endoscopy Amendment May 15 2014
3372_RATE_aetna fsf AMFSH CA02 5-11-15
1073_AFF_PMI URGENT CARE_HCP_(A-2)_03.01.10 CHANGE OF ADDRESS
5383_CORR_2014_Cal MediConnect Addendum-Windsor Gardens
[TABLE="width: 823"]
<tbody>[TR]
[TD]1146_SUMM_Cigna-PPO_04.01.2017_ Prov Saint John's Facility Renewal DocuSign Signature Summary[/TD]
[/TR]
</tbody>[/TABLE]





3686_FIN_2006.04_CIGNA
6571_LANG_SFV_AMR (A-1)_1986


...As you can see, some only have a year, some have only the year & month, it varies greatly. So I'm looking for the greatest of Excel minds. Any ideas?

Thanks in advance for your help!

 

[Stiuart_W]

I think you have too much variation even i you assume UK format dates (which seeing the variation would be a mistake in my opinion). So my advice would be do it by hand. The time it would take to automate you could do it by hand in that time

 

[drakepertuit]

The first 600 lines or so I was able to do Find/Replace on a dash or underscore. But still 1,000+ in the format described. This is a very tough one.. it very well may end up with an intern :/

 

[Joe4]

Welcome to the Board!

I often tell people that programming isn't "magic", it can only do what you tell it.
What that means is that if you can clearly define all the possibilities and rules for each possibilities, then you can usually write a program to do that (though it may be quite complex and long). However, if you are unable to define all the possibilities/rules, you have nothing which to program from.

In cases like this, I usually advise to go back to the source of the information and request more consistent data.
There are few things worse for a programmer than trying to program from bad or inconsistent data. Bad data usually leads to bad results!

 

[drakepertuit]

Thanks for the reply! This is a premier client undergoing implementation which was why it was accepted in the first place. All the different variations I could find are listed, but it's still quite the undertaking. It's beyond a shadow of a doubt that it will be faster to do the 1,000 lines or so by hand (thank you interns), however my thinking was that if a master string (1 module to rule them all) were created, one could ascend to Mr Excel glory as did the writer of the fuzzyVlookup module Thanks again for the reply!

 

[Joe4]

Ah yes, I am familiar with "premier" clients (that is usually corporate-speak meaning we want them so bad, we will take anything they send us!)
I have had to convert many a printed report sent to file for large clients (when we asked for a data extract).
Messy, but at least they were consistent and predictable once you figured out the pattern.

If this is a one-time implementation, and there are only 1000 lines, it would probably take longer to program and test than it would to just do it manually.
If it were a recurring thing, then it would make sense to try to program something.
Even if you were to program something, I think you would need someone to review all the results to make sure they seem reasonable and correct (I don't think I would be too confident that it would work for all records, sight unseen, especially when you may need to account for entry errors).