Require a formula to extract 2 dates from a string and place them into 2 separate cells

**JSD100** · 07-13-2022, 10:16 AM

Hi I am looking for a formula that would take dates from a string and place them in separate cells.

The type of text I need to extract from take the form of the of the following styles :

"AND 10 dates between 02/07/2021 - 03/08/2021" - I would need the extract to pull the 2 dates : 02/07/2021 03/08/2021 into 2 separate cells

"AND 2022/85 02/07/2021 & 03/08/2021" - I would need the extract to pull the 2 dates : 02/07/2021 03/08/2021 into 2 separate cells

"AND 2021/68 02/07/21 - 03/08/22" - (NB that the full year is not put as 02/07/2021 instead listed as 02/07/21 I would need the extract to pull the 2 dates : 02/07/2021 03/08/2021 into 2 separate cells

"AND 10 dates from & including 02/07/2021 - 03/08/2021" - I would need the extract to pull the 2 dates : 02/07/2021 03/08/2021 into 2 separate cells

I think I have covered all of the options that would occur.

is there a way for the formula to look at the specific of **/**/**** and also **/**/** and extract based on those conditions ?

Many thanks in advance

**AliGW** · 07-13-2022, 10:18 AM

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

**AliGW** · 07-13-2022, 10:36 AM

First date:

=DATEVALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A1,LEN(A1)-11),"-",""),"&",""))," ",REPT(" ",99)),98))," ",REPT(" ",99)),98)))

Second date:

=DATEVALUE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),98)))

Format cells as SHORT DATE.

**Glenn Kennedy** · 07-13-2022, 10:53 AM

or:

=TRANSPOSE(FILTERXML("<A>"&SUBSTITUTE(TRIM(SUBSTITUTE(A2,"&",""))," ","")&"</A>","//B[ (contains(.,'/') or contains(.,'-')) and string-length(.) >=8 ]"))

which will deal with dd/mm/yy and dd-mm-yy separated dates. It's also happy if there is more text AFTER the dates.

**AliGW** · 07-13-2022, 10:53 AM

Any questions?

**JSD100** · 07-13-2022, 10:56 AM

Hi, that works for the first formula : =DATEVALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A1,LEN(A1)-11),"-",""),"&",""))," ",REPT(" ",99)),98))," ",REPT(" ",99)),98))), how do i add additional condition such as : and as well as - ?

The second formula : =DATEVALUE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),98))) as i have just noticed that there are also some dates that read 08/03/21 - 09/3/2021 and also vice versa, so a full year type (2021) mixed in with a short year type (21)

**AliGW** · 07-13-2022, 10:58 AM

OK - so you didn't account for all eventualities after all. Go with Glenn's, which I think will work.

Next time you ask a question like this, please ensure that the sample data is realistic and provided in a sample workbook. Thanks.

**Glenn Kennedy** · 07-13-2022, 11:01 AM

BtW, mine will fall over if the non date string with a / is >=8 characters e.g. 2022/850

**AliGW** · 07-13-2022, 11:27 AM

Thanks for the kind rep comment.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

**JSD100** · 07-14-2022, 06:46 AM

Hi, encountered a slight problem, there are a couple of dates in the mix that have the sequence that follows :

19/11/2021- 05/12/2011 which produces 19/11/2021- in the first cell

How would I incorporate this into the formula so that it gets rid of the - or any other potential item after the date ?

The rest of the formula works really well, didnt see this one until I ran your formula and it threw this up as a problem

**Glenn Kennedy** · 07-14-2022, 06:52 AM

I'm sorry, but it's not clear what is in the cell and what you expect to see. Post an Excel sheet with before/after.

**Glenn Kennedy** · 07-14-2022, 06:55 AM

OK. I get it now.... Are there any - separated dates, e.g. 23-07-2021 in your dataset??

**Glenn Kennedy** · 07-14-2022, 06:57 AM

If there are NONE, then this works:

=TRANSPOSE(FILTERXML("<A>"&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"&",""))," ","")&"</A>","//B[ (contains(.,'/') or contains(.,'-')) and string-length(.) >=8 ]"))

**JSD100** · 07-14-2022, 07:02 AM

Please see attached sheet

**AliGW** · 07-14-2022, 07:04 AM

You need to include at least ONE example of EVERY format we need to take into consideration - two identical examples are not enough.

Please put together something comprehensive. Thanks.

**Glenn Kennedy** · 07-14-2022, 07:07 AM

Iteration 3, based on the additions at Post 14...

=TRANSPOSE(FILTERXML("<A>"&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"-"," "),"&",""))," ","")&"</A>","//B[ (contains(.,'/') or contains(.,'-')) and string-length(.) >=8 ]"))

**JSD100** · 07-14-2022, 07:12 AM

Absolutely fantastic result much appreciated Glenn and Ail for your patience with this.

Many thanks

John

**Glenn Kennedy** · 07-14-2022, 07:14 AM

Iteration 4... slight mistake in the previous one.

=TRANSPOSE(FILTERXML("<A>"&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"-"," "),"&",""))," ","")&"</A>","//B[ (contains(.,'/') ) and string-length(.) >=8 ]"))

**JSD100** · 11-01-2023, 10:29 AM

Thank you for this fantastic solution, however i have run into problems, the data that is being used has some additional variations, is there any way to adjust the formula to include (contains(.,':') & contains(.,'.') and also how do I adjust in future for any other variations I may encounter.

Any help is appreciated as always.

Thank you

**AliGW** · 11-01-2023, 10:33 AM

Remove the SOLVED tag if you'd like further assistance.

**JSD100** · 11-01-2023, 10:45 AM

Do I also re-post the extra question ?

**AliGW** · 11-01-2023, 10:48 AM

No. You have bumped the thread and removed the SOLVED tag - that's enough. Just wait patiently until someone offers help.

**CheeseSandwich** · 11-01-2023, 11:31 AM

Below is a different way of looking at it (as long as you have the functions available):

=LET(ts,--TEXTSPLIT(A2,{" ","-"}),TOROW(IF(LEN(ts)<>5,NA(),ts),2))

Would be good to see more examples of the date strings you mention.

**JSD100** · 11-03-2023, 05:32 AM

Hi this is the formula I have from another group member,

=TRANSPOSE(FILTERXML("<A>"&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"-"," "),"&",""))," ","")&"</A>","//B[ (contains(.,'/') or contains(.,'-')) and string-length(.) >=8 ]"))

and it works for the conditions that are within it, but I am looking to add more conditions to formula so that there is a greater variety of date options that can be evaluated.

The section that the formula checks for the date configuration has this bit contains(.,'/') or contains(.,'-')) its checks the de-limiter used in the date i.e. 10/12/2022 or 10-12-2022 and then extracts the dates into consecutive cells, I am looking to broaden the number of options it can handle and extract the relevant data.

Hope that gives a better insight into the formula I am using.

**AliGW** · 11-03-2023, 05:47 AM

Please provide an updated sample workbook with clear annotations.

**CheeseSandwich** · 11-03-2023, 05:48 AM

Originally Posted by AliGW

Next time you ask a question like this, please ensure that the sample data is realistic and provided in a sample workbook. Thanks.

Originally Posted by AliGW

You need to include at least ONE example of EVERY format we need to take into consideration

Originally Posted by Glenn Kennedy

I'm sorry, but it's not clear what is in the cell and what you expect to see. Post an Excel sheet with before/after.

Originally Posted by CheeseSandwich

Would be good to see more examples of the date strings you mention.

Please post some more examples of the data you have.

**JSD100** · 11-03-2023, 06:14 AM

Hi sorry, here is an example work book of the current formula and some examples of what it can and cant handle.

Cheers again

**AliGW** · 11-03-2023, 06:18 AM

In C6 copied down:

=--SUBSTITUTE(TRIM(TEXTSPLIT(B6,{"-",":",",","to"})),".","/")

Format all the results columns as SHORT DATE.

EDIT: No - this doesn't work. Back to the drawing board ...

**AliGW** · 11-03-2023, 06:24 AM

Is a "-" between dates meant to be dealt with like "to"?

**JSD100** · 11-03-2023, 06:39 AM

Yes it is, thank you

**AliGW** · 11-03-2023, 06:43 AM

Try this, then:

=IF(B6="","",LET(d,SUBSTITUTE(B6,".","/"),IF(NOT(ISERROR(FIND(" to ",d))),SEQUENCE(,(MID(d,15,10)+0)-(LEFT(d,10)+0)+1,--LEFT(d,10),1),--TRIM(TEXTSPLIT(d,{":",","," - "})))))

No doubt there will be more things you haven't considered, though ...

**CheeseSandwich** · 11-03-2023, 07:13 AM

Here is another one to try:

Please Login or Register  to view this content.

**AliGW** · 11-03-2023, 07:15 AM

That doesn't work for those with "to" that require a range.

**JSD100** · 11-03-2023, 07:15 AM

Absolutely fantastic, works brilliantly, thank you so much.

**JSD100** · 11-03-2023, 07:17 AM

Thank you great solution

**AliGW** · 11-03-2023, 07:20 AM

Oh! Well that's NOT what your expected results show, but try this:

=IF(B6="","",LET(d,SUBSTITUTE(B6,".","/"),IF(OR(NOT(ISERROR(FIND(" to ",d))),NOT(ISERROR(FIND(" - ",d)))),SEQUENCE(,(MID(d,15,10)+0)-(LEFT(d,10)+0)+1,--LEFT(d,10),1),--TRIM(TEXTSPLIT(d,{":",","," - "})))))

**AliGW** · 11-03-2023, 07:21 AM

They can't both be right, as they produce different results!!!

I don't think you are being thorough enough in your testing.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

**CheeseSandwich** · 11-03-2023, 07:49 AM

Originally Posted by AliGW

That doesn't work for those with "to" that require a range.

This version will work with the 'to' ranges, however, it won't work if there are seperate ranges AND to ranges in the same string like:
"11/03/2022 - 12/04/2022 11.10.2023 to 12.10.2023"

Please Login or Register  to view this content.

**AliGW** · 11-03-2023, 08:09 AM

It doesn't work, though, with ranges like that in B6 (see the OP's reply to me in post #30 and my update in post #36).

Mine won't cope with two ranges in a cell, but there aren't any of those in the sample file ... Probably to be continued!!!

**CheeseSandwich** · 11-03-2023, 08:22 AM

Sorry i missed post 30, i see what you mean now. Below is my updated formula given the heads up about post 30. I will wait for the OP now.

Please Login or Register  to view this content.

**HansDouwe** · 11-03-2023, 10:08 AM

Another solution that does everything.

Please try in C6 and copy down:

Please Login or Register  to view this content.

Require a formula to extract 2 dates from a string and place them into 2 separate cells

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Re: Require a formula to extract 2 dates from a string and place them into 2 separate cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Lookup formula to separate dates/numbers out of a text string

[SOLVED] Require Formula to extract data to then populate multiple cells

Bookmarks

Bookmarks

Posting Permissions