extracting date from cell in a different spreadsheet

[fawnlemur]

Hi all

I'm not too sure if this is possible or not, but thought I'd ask just

I have a list of 100,000 cells which go like this [FC 12/06 Review 27/06], [DC 15/06 reviewed 29/06], [LC 13/06 reviewed 27/06]

this list is sent to me daily with different data in it, so I'm not able to amend anything in that spreadsheet.

I was wondering is there a way to get the oldest review date and the newest review date for the cells starting with FC, DC and LC separately in a new spreadsheet.

so I will have the spreadsheet that is sent to me in a folder and this new spreadsheet will extract the data from it every time the code is run.

I'm a beginner and have no idea on how to do this. please help

Thank you for taking your time to help me out

[Gatti]

So, the date is always before the "]" character, right?

You can make a column with the extracted date to get MIN and MAX

Something like:

Please Login or Register  to view this content.

(Replace A1 with the cell)

[FDibbins]

[FC 12/06 Review 27/06]

which is the date - 12/06 or 27/06?
I understand that you cant amend the cell,s but can you add a formula/s to it (each time) to extract the different pieces on info?
How many different types of info are there, and how many per entry?

[fawnlemur]

Hi

I want to explain the I meant the [] to represent the cell (apologies for the misunderstanding).


I want the reviewed date so the 2nd date


the spreadsheet is send daily so unless you can get a code in the other spreadsheet (where the code will be) to edit that spreadsheet then yes we can edit.


as others will be doing this I do'nt want them to be editing the spreadsheet manually.



there are 3 types of info FC, DC and LC
Thank you

[fawnlemur]

Can anyone help please?

[Marc L]

Hi !

Attach a before workbook and an after workbook with expected result or
if separate worksheets just a workbook with accurate before & after worksheets …

[FDibbins]

OK, 27/06 is not really a date as far as excel is concerned, so assuming this would be for 2018, try this as a 1st step to get the dates (adjust the file names and cell refs as needed)
=--(TEXT(MID(Sheet1!G13,SEARCH("review",Sheet1!G13)+7,5)&"/2018","ddmmyy"))

I think this should work with the UK date format, if not get back to me and I will do something different.