Is it possible to extract any dates from a section of text?

**Gooford** · 11-21-2011, 05:50 AM

Hi,

I am not too hopeful on this, but thought it would be worth asking.

I have a report in excel showing transactions over the last four years. Unfortunately in many cases the Invoice Date field has not been completed, but there is a field entitled [Notes] which contains date information amongst text, e.g:

[Notes]
"Invoice for personal assistant on 5/6/11"

Is it possible to extract this date and enter it into the [invoice date] column.

If it is possible, what would happen if there were two dates, e.g:

"Invoices for personal assistant on 5/6/11 and 17/6/11"

Thanks and regards

**Azam Ali** · 11-21-2011, 06:27 AM

see this file find date in text.xls

**Gooford** · 11-21-2011, 07:14 AM

Hi,

Thanks.

I have over 4000 transactions and the text is not in a consistent format so the date may be in any position within the cell.

Will solution 2 pick this up?

**Azam Ali** · 11-21-2011, 07:19 AM

if you want to pick first date then try solution 2
however to pick the second date you have to modify the formula

**Gooford** · 11-21-2011, 07:23 AM

Excellent thanks!

**Gooford** · 11-21-2011, 08:21 AM

Could you let me know what the ... $H$2,"" part of the formula does?

**Azam Ali** · 11-22-2011, 12:01 AM

it is actually the part of substitute function. when you pick the date through mid function a double comma " at the end is also picked and substitute function is removing it

It is recommended to always use evaluate formula option in the formula tab in order to understand the formula