Extract date from text

[Neokey]

Hi All,

I wonder if anyone can help?

I have a workbook that contains a date in a text cell and I would like to create a function to extract the date, I would also like to format the cell as a date after the information has been extracted.

Oh and I forgot to mention that not all the cells have text so would it be possible to ignore the cell that are blank?

Below is an example of the text in the cell with the date: -

Payment# 328604 on 09/14/18 for GBP 231.00 Thank you for looking and I would be really grateful for any help.

<colgroup><col width="387"></colgroup><tbody>
</tbody>

 

[Eric W]

Welcome to the forum.


If all your cells are similar to your example, with the date formatted as mm/dd/yy, and no other slashes in the cell, then something like

=IFERROR(MID(A1,FIND("/",A1)-2,8)+0,"")

would work. Format the result cell(s) as a date. If your cells have differing formats (and they usually do), then it's a much tougher proposition.

 

[Special-K99]

Assuming all dates are 8 characters in length, I cant tell cos youve only supplied one item of test data.

=IFERROR(TEXT(MID(A1,FIND("on",A1)+3,8)+0,"dd/mm/yy"),"")

 

[Neokey]

Many thanks for all your help, I have tested both formulas, but the formulas are both returning no dates in some cases and dates for others and I cannot see the reason why?

Here's a few examples: -

Payment	Test Formula 1	Test Formula 2
Payment# 318310 on 07/13/18 for GBP 5.18
Payment# 322013 on 08/03/18 for GBP 45.50	08/03/18	08/03/2018
Payment# 318310 on 07/13/18 for GBP 83.32
Payment# 318310 on 07/13/18 for GBP 58.81
Payment# 318310 on 07/13/18 for GBP 71.18
Payment# 318310 on 07/13/18 for GBP 46.87
Payment# 318310 on 07/13/18 for GBP 34.06
Payment# 318310 on 07/13/18 for GBP 20.82
Payment# 318310 on 07/13/18 for GBP 36.84
Payment# 318310 on 07/13/18 for GBP 85.49
Payment# 318310 on 07/13/18 for GBP 36.84
Payment# 318310 on 07/13/18 for GBP 25.34
Payment# 322013 on 08/03/18 for GBP 49.96	08/03/18	08/03/2018
Payment# 318310 on 07/13/18 for GBP 88.55
Payment# 318310 on 07/13/18 for GBP 34.81
Payment# 318310 on 07/13/18 for GBP 11.45
Payment# 318310 on 07/13/18 for GBP 7.56
Payment# 318310 on 07/13/18 for GBP 98.50
Payment# 316703 on 07/06/18 for GBP 226.84	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 151.57	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 109.52	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 117.52	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 117.52	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 46.87	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 151.57	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 46.87	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 95.98	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 199.31	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 46.87	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 2.78	07/06/18	07/06/2018
Payment# 316703 on 07/06/18 for GBP 6.46	07/06/18	07/06/2018
Payment# 318310 on 07/13/18 for GBP 124.20

<tbody>
</tbody>

Thanks for looking

 

[jtakw]

Hi,

Another way, format result cell as Date:

Book1
	A	B
1	Payment# 328604 on 09/14/18 for GBP 231.00	9/14/2018
Sheet330

Cell Formulas
Range	Formula
B1	=IFERROR(MID(A1,SEARCH("??/??/??",A1),8)+0,"")

 

[Neokey]

I tried the formula and even though I formatted the resulting cells as a date I am still missing some dates.

I'm not sure why, but all the tests are only returning the date from certain cells?

Thank you for all your help so far.

 

[Fluff]

The dates in the report are mm/dd/yy, but I suspect that you use dd/mm/yy

 

[Fluff]

Try
=IFERROR(DATE(MID(A2,FIND("/",A2)+4,2),MID(A2,FIND("/",A2)-2,2),MID(A2,FIND("/",A2)+1,2)),"")

 

[Neokey]

Thank you for your help, the formula worked, but the years on all the dates are incorrect.

This is an example of the date the formula returned: -

29/06/1918

29/06/1918

29/06/1918

22/06/1918

<colgroup><col></colgroup><tbody>
</tbody>

 

[mrshl9898]

=IFERROR(DATE(20&MID(A2,FIND("/",A2)+4,2),MID(A2,FIND("/",A2)-2,2),MID(A2,FIND("/",A2)+1,2)),"")

Can't find a date that isn't there, this assumes 2000 by putting 20 in front of the year