Extract a Date from a Cell which is amongst text

[Linda Hunter]

Hi, I'm wanting to extract a date from a cell that is amongst text.

Col A,B,C,D and E (of attached worksheet) are a copy and paste from a system that we use to track action items.

Col E includes Action Item # + Due Date + Action Required. We need to extract the date by formula into Col F "Due Date". Currently we are manually typing, but as you know this can led to error.

Please find worksheet attached (due to the sensitivity of the information the text has been replaced with xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.

Hope you can help, and that's for this forum to help make our lives easier.

Linda

 

[Khalid NGO]

Hi Lina,

Try this in F10
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E10,"4. (due date ",""),"x",""),")","")

 

[Sathish KV]

Hi,

You can also try the below formula

=INT(MID(E10,FIND("(",E10)+10,FIND(")",E10)-(FIND("(",E10)+10)))

Change the format of column F to 'Date' for conditional formatting to work

 

[Linda Hunter]

Thanks Khalid. That works great, however the "x" is actually text (the x was provided in the worksheet just to protect sensitive information), so will we need to change each line item formula with the actual text in Col E? If so, it would be quicker just to do the manual entry of the date. The line item text in Col E changes on a regular basis as the actions are closed in our system.

Hope this makes sense?

Linda

 

[Sathish KV]

Hi,

You can use the formula I have posted above which uses the brackets as constant

 

[Linda Hunter]

Sathish, that works perfectly. thank you so much.

Linda

 

[Khalid NGO]

Thanks Khalid. That works great, however the "x" is actually text (the x was provided in the worksheet just to protect sensitive information), so will we need to change each line item formula with the actual text in Col E? If so, it would be quicker just to do the manual entry of the date. The line item text in Col E changes on a regular basis as the actions are closed in our system.

Hope this makes sense?

Linda

Hi Linda,
Oops i forgot to copy down my formula.

Thanks to Sathish for perfect answer.
However i am also trying alternate solutions.
Thank you.

 

[Faseeh]

Hi All,

This one also, format cells as date:

=TRIM(MID(E10,FIND("/",E10)-2,FIND(")",E10)-FIND("/",E10)+2))+0

 

[Somendra Misra]

Hi,

You can also try this:

=DATEVALUE(MID(E10,SEARCH("due date",E10)+9,FIND(")",E10)-(SEARCH("due Date ",E10)+9)))

Regards,