# Extract from Crazy Dates!

**URL:** <https://forum.openrefine.org/t/extract-from-crazy-dates/1705>\
**Category:** Support and Helpdesk\
**Created:** [August 9, 2024, 9:39pm UTC](https://forum.openrefine.org/t/extract-from-crazy-dates/1705 "2024-08-09T21:39:15Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mcyzyk](https://avatars.discourse-cdn.com/v4/letter/m/5f9b8f/32.png) [@mcyzyk](https://forum.openrefine.org/u/mcyzyk)\
**Post date:** [August 9, 2024, 9:39pm UTC](https://forum.openrefine.org/t/extract-from-crazy-dates/1705/1 "2024-08-09T21:39:15Z")

</div>

Ok, here is a puzzler:

I have "date" values in all kinds of crazy "formats":

[Germany] : [Unknown] [19th century]  
[Gotha : Justus Perthes] [ca. 1850]  
[Gotha : Justus Perthes] 1824  
[London] : [Heather] 18082  
[London] : Henry Hansard Printer 1850 ([London] : Standidge & Co. Litho. Old Jewry)  
[London] 1671  
[London] 1782  
[Maastricht] : [Di Georgi] [1845]  
[Madrid] : publisher not known [1622]  
[Madrid] [1622]  
[Ms] 1794  
[Netherlands] : G.S. de Tempe [1868]  
[Nürnberg] : [Homannianes Heredes] 1740  
[Paris : Binet] [1839]  
[Paris] : [Charles-Antoine Jombert] 1752  
[Paris] 1775  
[Plaats van uitgave niet vastgesteld] : [uitgever niet vastgesteld] [1752]  
[Plaats van uitgave niet vastgesteld] : Topographisch Bureau [1825]  
[Plaats van uitgave niet vastgesteld] [circa1840]  
[Plaats van uitgave niet vastgesteld] 1752  
1824

One thing they all have in common is that somewhere in them is a number YYYY.

How to extract?

Reading GREL docs on RegEx....

---

<div class="post-metadata">

**Author:** ![mcyzyk](https://avatars.discourse-cdn.com/v4/letter/m/5f9b8f/32.png) [@mcyzyk](https://forum.openrefine.org/u/mcyzyk)\
**Post date:** [August 10, 2024, 2:37am UTC](https://forum.openrefine.org/t/extract-from-crazy-dates/1705/2 "2024-08-10T02:37:59Z")

</div>

Progress so far:

This does most of what I want:  
value.match(/(^|^.\*?\D)(\d{4})($|0$)/)[1]

but leaves values like this:  
16980

This, then, cleans that:  
value.match(/(^|^.\*?\D)(\d{4})($|0$)/)[1]  
results in  
1698

Now wondering how I can merge these two RegEx'es?....

---

<div class="post-metadata">

**Author:** ![thadguidry](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/thadguidry/32/653_2.png) [@thadguidry](https://forum.openrefine.org/u/thadguidry)\
**Post date:** [August 10, 2024, 4:54am UTC](https://forum.openrefine.org/t/extract-from-crazy-dates/1705/3 "2024-08-10T04:54:48Z")

</div>

Why not use facets like the Text Filter which also supports RegEx.  
And then do the extraction of dates in various formats in 2 or 3+ passes?  
Why does the extraction have to be in a single operation step in OpenRefine?  
Where will the extracted dates go? Into a new column?

1. Filter on dates (4 digit numbers) in a cells' value using `value.find(/\d{4}/)`
  - extract them into a column called DATE with `value.find(/\d{4}/).join("|")` because `find()` outputs an array of elements, so joining them with a pipe separator, just in case you have multiple 4 digit dates in a string?

2. Filter on dates having `th century` using `value.contains("th century")` ?
  - extract or find them into the same column called DATE using `value.find(/(\d{2})th century/).join("|")`

3. Filter on other date patterns discovered using Facets, maybe Roman Numerals?
  - extract them into the same column called DATE or a new column WEIRD-DATES

---

<div class="post-metadata">

**Author:** ![mcyzyk](https://avatars.discourse-cdn.com/v4/letter/m/5f9b8f/32.png) [@mcyzyk](https://forum.openrefine.org/u/mcyzyk)\
**Post date:** [August 10, 2024, 12:26pm UTC](https://forum.openrefine.org/t/extract-from-crazy-dates/1705/4 "2024-08-10T12:26:43Z")

</div>

> [@thadguidry](#):
>
> Why not ... do the extraction of dates in various formats in 2 or 3+ passes?

Because I was apparently questing for the One RegEx to Rule Them All?!

As I went to sleep last night this thought of your popped into my head:

I have two routines that ultimately result in what I want. Just run one, then run the other.

Not sure why I was so fixated on the notion of a Single Pass!

Your advice, MUCH appreciated,

Mark