Extract Latest Date from wall of text

[lordfa9]

Lets say i have a wall of text that looks like this, all of it in 1 cell

XXXXXXX ABC 22/04/14, AAAAAAAAAAAAA ABC 17/04/14, FFFFFFF ABC 01/04/14, TGHYTG ABC 22/04/14, HFFA AGF 02/04/14 & 02/05/14.

What formula do I use to extract the latest date from this string? In this example, the ideal result will be 02/05/2014

Some considerations:

• the wall of text can be very very long
• the desired output date can be anywhere in the string, it could be at the start one day and at the very end the next, it can be hidden behind an & or maybe not.

 

[Rick Rothstein]

Give this array-entered** formula a try...

=MAX(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)),--MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[lordfa9]

Give this array-entered** formula a try...

=MAX(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)),--MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Works for the sample data but returns a "Dates and times that are too negative or too large" message when I use my actual data

is this formula limited to length of text? another thing is that the live data is alphanumeric so

XXXXXXX ABC 22/04/14 could have a mix of letters and numbers in the XXXXXXX portion</pre>

 

[Rick Rothstein]

Works for the sample data but returns a "Dates and times that are too negative or too large" message when I use my actual data

is this formula limited to length of text? another thing is that the live data is alphanumeric so

XXXXXXX ABC 22/04/14 could have a mix of letters and numbers in the XXXXXXX portion

No way to know what is wrong unless you show us the text that the formula is failing for... then I can debug it to find out what is wrong.

 

[lordfa9]

Well I guess it can't hurt (though I'll take it down once we're done so I appreciate no quoting)

Pulled out a sample from the live data, for testing it's probably fine if we copy paste the string about 15-20 times (thats about the actual length of the data too) and change a few dates here and there

EP-CON-000107 CON 16/04/14 & 16/04/14, EP-CON-000134 CON 22/04/14, EP-10010074 CAP 22/04/14, EP-L-000004 IND 22/04/14, EP-LOGIC-000003 IC  24/04/14, EP-INTER-000009 IC  24/04/14,

 

[icestationzbra]

Extract Latest Date from wall of textCrossposted

 

[Rick Rothstein]

The problem with my formula was your XXXXX's in the original text you posted contained number that were long enough to fool the test I used. Try this array-entered formula instead...

=MAX(IF(ISNUMBER(FIND("/",MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8))+MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)),--MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)))

 

[lordfa9]

The problem with my formula was your XXXXX's in the original text you posted contained number that were long enough to fool the test I used. Try this array-entered formula instead...

=MAX(IF(ISNUMBER(FIND("/",MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8))+MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)),--MID(A1,ROW(INDIRECT("1:"&LEN(A1)-7)),8)))

sweet this hits the spot! Thanks!