Extract Date from Text

[Barieq]

Hi,

I have a huge data in which Column D has text date starting from D6, I need to extract both the dates within the brackets into column E & G. Sample workbook attached.


Barieq

[jindon]

See if this works.(working here)

Please Login or Register  to view this content.

[AliGW]

Formula solution - in E6 copied down:

=IFERROR(VALUE(MID(D6,FIND("|",D6)+2,10)),VALUE(MID(D6,FIND("|",D6)+2,6)&MID(D6,FIND("|",D6)+8,4)+579))

In F6 copied down:

=IFERROR(VALUE(MID(D6,FIND("|",D6)+16,10)),VALUE(MID(D6,FIND("|",D6)+16,6)&MID(D6,FIND("|",D6)+22,4)+579))

[Rick Rothstein]

If the dates always appear at the end of the text with double digit days and months as shown in all your examples, then these two simple formulas can be used...

E6: =MID(D6,LEN(D6)-26,11)

F6: =MID(D6,LEN(D6)-11,11)

[AliGW]

I think that pulls out text rather than a real date, though, Rick. Correct me if I'm wrong!

[Rick Rothstein]

Whoops! Thanks Ali (it's almost 4:30 in the morning here and I'm about to go to sleep)... I forgot the 0+...

E6: =0+MID(D6,LEN(D6)-26,11)

F6: =0+MID(D6,LEN(D6)-11,11)

Note: You will have to format each of these cells with the date format of your choice before copying the formulas down.

[Rick Rothstein]

By the way, if a macro solution is desired and using the same assumptions, we can use the formulas I posted above as a basis...

Please Login or Register  to view this content.

[ArdusPetus]

Here is a solution with a VBA macro.

Before running the macro, define name ListHeader to point to the header above the list of dates.

The macro first attempts to calculate a Gregorian date. If it fails, it retries with Hijri date.

Please Login or Register  to view this content.

HTH,
--
LR

[Barieq]

Thank you all for your code and formula. I really appreciate.

[Barieq]

@AliGW your formula result is #value

[jindon]

You are welcome and thanks for the rep.

Please don't forget to mark the thread as Solved.

[Barieq]

@ArdusPetus

I like the code which check the Hijri Date, but some of the rows the date displayed is wrong. Can you please look into this.

Some Text | 01/11/1440 To 29/10/1441 {04-Jul-2019 To 21-Jun-2020} 9/21/2018 6/20/2020
Some Text | 01/11/1440 To 29/10/1441 {04-Jul-2019 To 21-Jun-2020} 9/21/2018 6/20/2020
Some Text | 09/07/1440 To 08/07/1441 {16-Mar-2019 To 03-Mar-2020} 5/11/2019 3/31/2020

[Barieq]

@jindon,

Sorry, I have noticed that you code displays wrong year. All the dates related to 2019 is extracted as 2020.

[AliGW]

@AliGW your formula result is #value

No, they work fine - see the attachment.

[jindon]

@jindon,

Sorry, I have noticed that you code displays wrong year. All the dates related to 2019 is extracted as 2020.

Ahhh,

Try this then

Please Login or Register  to view this content.

[Barieq]

No, they work fine - see the attachment.

@AliGW,

When I drag the formula from E6 some cells result is #VALUE!. Even the file attached by you showing wrong result. All the dates in the test is dd/mm/yyyy format. Check below the result.

Some Text | 01/04/2019 To 31/03/2020 {01-Apr-2019 To 31-Mar-2020} 01-Apr-19 31-Mar-20
Some Text | 25/09/1440 To 24/09/1441 {30-May-2019 To 17-May-2020} #VALUE! #VALUE!
Some Text | 10/06/2019 To 09/06/2020 {10-Jun-2019 To 09-Jun-2020} 06-Oct-19 06-Sep-20
Some Text | 10/06/2019 To 09/06/2020 {10-Jun-2019 To 09-Jun-2020} 06-Oct-19 06-Sep-20
Some Text | 17/10/1440 To 16/04/1441 {20-Jun-2019 To 13-Dec-2019} #VALUE! #VALUE!
Some Text | 17/10/1440 To 16/04/1441 {20-Jun-2019 To 13-Dec-2019} #VALUE! #VALUE!
Some Text | 01/11/1440 To 29/10/1441 {04-Jul-2019 To 21-Jun-2020} 11-Jan-19 #VALUE!
Some Text | 01/11/1440 To 29/10/1441 {04-Jul-2019 To 21-Jun-2020} 11-Jan-19 #VALUE!
Some Text | 20/12/1440 To 19/12/1441 {21-Aug-2019 To 09-Aug-2020} #VALUE! #VALUE!
Some Text | 20/08/2019 To 19/02/2020 {20-Aug-2019 To 19-Feb-2020} #VALUE! #VALUE!
Some Text | 17/04/1441 To 16/10/1441 {14-Dec-2019 To 08-Jun-2020} #VALUE! #VALUE!
Some Text | 17/04/1441 To 16/10/1441 {14-Dec-2019 To 08-Jun-2020} #VALUE! #VALUE!
Some Text | 01/01/2020 To 30/06/2020 {01-Jan-2020 To 30-Jun-2020} 01-Jan-20 #VALUE!
Some Text | 01/01/2020 To 30/06/2020 {01-Jan-2020 To 30-Jun-2020} 01-Jan-20 #VALUE!
Some Text | 24/07/1440 To 23/01/1441 {31-Mar-2019 To 22-Sep-2019} #VALUE! #VALUE!
Some Text | 24/01/1441 To 23/07/1441 {23-Sep-2019 To 18-Mar-2020} #VALUE! #VALUE!
Some Text | 09/07/1440 To 08/07/1441 {16-Mar-2019 To 03-Mar-2020} 07-Sep-19 07-Aug-20

[jindon]

See my post #15

https://www.excelforum.com/excel-pro...ml#post5302881

[AliGW]

I am sorry, but my formulae work:

Excel 2016 (Windows) 32 bit

	D	E	F
5	Header	Date From	Date To
6	Some Text | 01/04/2019 To 31/03/2020 {01-Apr-2019 To 31-Mar-2020}	01-Apr-19	31-Mar-20
7	Some Text | 25/09/1440 To 24/09/1441 {30-May-2019 To 17-May-2020}	25-Sep-19	24-Sep-20
8	Some Text | 10/06/2019 To 09/06/2020 {10-Jun-2019 To 09-Jun-2020}	10-Jun-19	09-Jun-20
9	Some Text | 10/06/2019 To 09/06/2020 {10-Jun-2019 To 09-Jun-2020}	10-Jun-19	09-Jun-20
10	Some Text | 17/10/1440 To 16/04/1441 {20-Jun-2019 To 13-Dec-2019}	17-Oct-19	16-Apr-20
11	Some Text | 17/10/1440 To 16/04/1441 {20-Jun-2019 To 13-Dec-2019}	17-Oct-19	16-Apr-20
12	Some Text | 01/11/1440 To 29/10/1441 {04-Jul-2019 To 21-Jun-2020}	01-Nov-19	29-Oct-20
13	Some Text | 01/11/1440 To 29/10/1441 {04-Jul-2019 To 21-Jun-2020}	01-Nov-19	29-Oct-20
14	Some Text | 20/12/1440 To 19/12/1441 {21-Aug-2019 To 09-Aug-2020}	20-Dec-19	19-Dec-20
15	Some Text | 20/08/2019 To 19/02/2020 {20-Aug-2019 To 19-Feb-2020}	20-Aug-19	19-Feb-20
16	Some Text | 17/04/1441 To 16/10/1441 {14-Dec-2019 To 08-Jun-2020}	17-Apr-20	16-Oct-20
17	Some Text | 17/04/1441 To 16/10/1441 {14-Dec-2019 To 08-Jun-2020}	17-Apr-20	16-Oct-20
18	Some Text | 01/01/2020 To 30/06/2020 {01-Jan-2020 To 30-Jun-2020}	01-Jan-20	30-Jun-20
19	Some Text | 01/01/2020 To 30/06/2020 {01-Jan-2020 To 30-Jun-2020}	01-Jan-20	30-Jun-20
20	Some Text | 24/07/1440 To 23/01/1441 {31-Mar-2019 To 22-Sep-2019}	24-Jul-19	23-Jan-20
21	Some Text | 24/01/1441 To 23/07/1441 {23-Sep-2019 To 18-Mar-2020}	24-Jan-20	23-Jul-20
22	Some Text | 09/07/1440 To 08/07/1441 {16-Mar-2019 To 03-Mar-2020}	09-Jul-19	08-Jul-20

Sheet: Sheet1

I am not in the US - dates in the UK are also dd/mm/yyyy.

Even the file attached by you showing wrong result.

Not here!

[Barieq]

Example look into row 7 Actual Date is {30-May-2019 To 17-May-2020} but your result is something else. I don't know how you say your formula works.

[AliGW]

OK - I give up. You said my formulae were returning #VALUE! errors - they were not

Use one of the other solutions, and PLEASE stop quoting posts unnecessarily - it's just clutter.

[Barieq]

See my post #15

https://www.excelforum.com/excel-pro...ml#post5302881

@Jindon,

Perfect, You are always genius.

[AliGW]

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

[jindon]

Glad it works.

Dates in Excel is a nightmare...

[mjr veverka]

... especially for settings other than English-American ...

[Barieq]

... especially for settings other than English-American ...

Yes, I agree.

[Barieq]

OK - I give up. You said my formulae were returning #VALUE! errors - they were not

Use one of the other solutions, and PLEASE stop quoting posts unnecessarily - it's just clutter.

Now it is Solved.

[AliGW]

It's the US dates that bugger everything up! Why they have to do it the wrong way round, goodness only knows ...

[PCI]

Here a Jindon's code remake which seems giving good result
BTW @Jindon could you explain the purpose of

Please Login or Register  to view this content.

Here the full code

Please Login or Register  to view this content.

[jindon]

Yeah, it should be 11, not 9, so it only returns 20 for year...

Please Login or Register  to view this content.

Array(1, 3) is to convert string like 01-Apr-2019 to a serial date as d/m/y.

[ArdusPetus]

@ArdusPetus

I like the code which check the Hijri Date, but some of the rows the date displayed is wrong. Can you please look into this.

What is wrong with the results you quote ? What should be the results ?

[PCI]

Array(1, 3) is to convert string like 01-Apr-2019 to a serial date as d/m/y.

Thx Jindon