Extract Date from text

[eddie9874]

Hi,

I need some help to extract date from texts, however the dates format are inconsistent and their position within the text is random but usually is toward the back part of the text. Examples as follows:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Text[/TD]
[TD]I would like to Extract[/TD]
[/TR]
[TR]
[TD]123456789 SMITH, C 40HR W/E 12.11.12[/TD]
[TD]12/11/2012[/TD]
[/TR]
[TR]
[TD]*4500273765 R TOM PE 05/11/2012 80 HRS[/TD]
[TD]05/11/2012[/TD]
[/TR]
[TR]
[TD]C INV 55662 BURN WE19/11/12 40 HRS[/TD]
[TD]19/11/2012[/TD]
[/TR]
[TR]
[TD]623111 MORRISON WE 26.11.2012 5 DAYS[/TD]
[TD]26/11/2012[/TD]
[/TR]
[TR]
[TD][TABLE="width: 404"]
<tbody>[TR]
[TD="width: 404"]24517983 - MONTH END 31-07-12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]31/07/2012[/TD]
[/TR]
</tbody>[/TABLE]













I don't really know where to start, any help is appreciated.

Regards,

Eddie

 

[Rick Rothstein]

Hi,

I need some help to extract date from texts, however the dates format are inconsistent and their position within the text is random but usually is toward the back part of the text. Examples as follows:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Text[/TD]
[TD]I would like to Extract[/TD]
[/TR]
[TR]
[TD]123456789 SMITH, C 40HR W/E 12.11.12[/TD]
[TD]12/11/2012[/TD]
[/TR]
[TR]
[TD]*4500273765 R TOM PE 05/11/2012 80 HRS[/TD]
[TD]05/11/2012[/TD]
[/TR]
[TR]
[TD]C INV 55662 BURN WE19/11/12 40 HRS[/TD]
[TD]19/11/2012[/TD]
[/TR]
[TR]
[TD]623111 MORRISON WE 26.11.2012 5 DAYS[/TD]
[TD]26/11/2012[/TD]
[/TR]
[TR]
[TD][TABLE="width: 404"]
<tbody>[TR]
[TD="width: 404"]24517983 - MONTH END 31-07-12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]31/07/2012[/TD]
[/TR]
</tbody>[/TABLE]

Do all of the dates in the cells always have a leading number for single digit months and days?

 

[eddie9874]

No, sometimes the date could appear as 5/11/2012 in the text.

 

[Rick Rothstein]

No, sometimes the date could appear as 5/11/2012 in the text.

How about the months?

 

[sheetspread]

Excel 2010
	A	B
2	123456789 SMITH, C 40HR W/E 12.11.12	12/11/12
3	*4500273765 R TOM PE 05/11/2012 80 HRS	05/11/2012
4	C INV 55662 BURN WE19/11/12 40 HRS	19/11/12 4
5	623111 MORRISON WE 26.11.2012 5 DAYS	26/11/2012
6	24517983 - MONTH END 31-07-12	31/07/12
Sheet4

Cell Formulas
Range	Formula
B2	=TRIM(SUBSTITUTE(SUBSTITUTE(IFERROR(MID(A2,SEARCH(".??.",A2,1)-2,10),"")&IFERROR(MID(A2,SEARCH("/??/",A2,1)-2,10),"")&IFERROR(MID(A2,SEARCH("-??-",A2,1)-2,10),""),".","/"),"-","/"))

Not sure what to expect on a larger sample (Row 4 has an unwanted digit at the end). Rick's regex solution will be better.

 

[eddie9874]

How about the months?

Double digits from I have seen so far

 

[Rick Rothstein]

Give this UDF a try...

[table="width: 500"]
[tr]
	[td]Function GetDate(ByVal S As String) As Date
  Dim DayPart As String, Parts() As String
  Parts = Split(Replace(Replace(Replace(S, " ", "x"), "-", "/"), ".", "/"), "/")
  DayPart = Right(Parts(UBound(Parts) - 2), 2)
  DayPart = Mid(DayPart, 2 + (Left(DayPart, 1) Like "#"))
  GetDate = DateSerial(Val(Parts(UBound(Parts))), Parts(UBound(Parts) - 1), DayPart)
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetDate just like it was a built-in Excel function. For example,

=GetDate(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[admiral100]

HI,

Another way :

=REPLACE(TEXT(REPLACE(REPLACE(MID(A2,IFERROR(SEARCH("??.??.",A2),IFERROR(SEARCH("??/??/",A2),SEARCH("??-??-",A2))),10),3,1,"/"),6,1,"/"),"dd/mm/yy"),9,2,"")


[TABLE="width: 365"]
<tbody>[TR]
[TD]Text[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]123456789 SMITH, C 40HR W/E 12.11.12[/TD]
[TD]12/11/12[/TD]
[/TR]
[TR]
[TD]*4500273765 R TOM PE 05/11/2012 80 HRS[/TD]
[TD]05/11/12[/TD]
[/TR]
[TR]
[TD]C INV 55662 BURN WE19/11/12 40 HRS[/TD]
[TD]19/11/12[/TD]
[/TR]
[TR]
[TD]623111 MORRISON WE 26.11.2012 5 DAYS[/TD]
[TD]26/11/12[/TD]
[/TR]
[TR]
[TD]24517983 - MONTH END 31-07-12[/TD]
[TD]31/07/12[/TD]
[/TR]
</tbody>[/TABLE]