Extract date from text (long date format)

[liiamra]

Hello,

I was wondering if it is possible to extract the date from a long text field. I have two problems, 1- Inconsistency across fields, 2- Long date format (day month year).

I have come across a solution by Rick Rothstein; it works perfectly but my problem is with the long date. Here is Rick's solution:http://www.mrexcel.com/forum/excel-questions/652771-excel-formula-extract-date-large-text-field.html

Examples of the fields I have are:

Down the valley Stories, 12:33, 6 August 2013, 553 words, (English)
The Bandi Rock, 08:17, 6 August 2013, 558 words, (English)
All in one News Service, 12:32, 6 August 2013, 610 words, (English)

If the date was formatted as 06.08.2013, then I would follow Rick's solution without any problems.

I have searched without any success! Any help is appreciated.

Regards//
liiamra

 

[Jonmo1]

Is the date ALWAYS after the 2nd comma ?

Try
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1)))+0

 

[liiamra]

Is the date ALWAYS after the 2nd comma ?

Try
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1)))+0

Thanks a million Jonmo, it works perfectly! but the only problem is that sometimes there is no time, so that there is only one comma before the date

Below is an example of the fields I have. If we can treat the second comma from the right, then it should work. I don't fully understand the logic behind the formula; that is why I am not able to alter!

Down the valley Stories, 12:33, 6 August 2013, 553 words, (English)
All in one News Service, 12:32, 6 August 2011, 610 words, (English)
Agence One, 30 July 2013, 209 words, (English)
The B Online, 19:18, 29 July 2013, 6062 words, (English)
Green Mail, 29 July 2013, 363 words, (English)

Thanks again - I appreciate your time,
All Best//
liiamra

 

[Jonmo1]

Perhaps

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)*(1+ISNUMBER(SEARCH(":",A1))),LEN(A1)))+0

Assuming there will only be a : in the string if it contains a time.

 

[liiamra]

Perhaps

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)*(1+ISNUMBER(SEARCH(":",A1))),LEN(A1)))+0

Assuming there will only be a : in the string if it contains a time.

Many thanks Jonmo, it worked perfectly - yeah indeed, there is only one :

I thank you again & again & wish you all best//
liiamra

 

[liiamra]

Dear Jonmo,


I just wonder about the reason for which the first formula indicates the current year always. The day and month are correct, but the year corresponds to the one on my system. For example text...December 23, 2009..text will become December 23, 2013. If I change the year on my system to 2010, then it becomes December 23, 2010 and so on.


Many thanks

 

[Jonmo1]

Works for me..
Perhaps the syntax of your given strings has changed...

This is using all the examples you provided, I manually changed a few of the years though..

Excel Workbook
	A	B	C
1	Given Examples	Fist formula	2nd Formula
2	Down the valley Stories, 12:33, 6 August 2013, 553 words, (English)	Tuesday, August 06, 2013	Tuesday, August 06, 2013
3	The Bandi Rock, 08:17, 6 August 1999, 558 words, (English)	Friday, August 06, 1999	Friday, August 06, 1999
4	All in one News Service, 12:32, 6 August 2013, 610 words, (English)	Tuesday, August 06, 2013	Tuesday, August 06, 2013
5	All in one News Service, 12:32, 6 August 2011, 610 words, (English)	Saturday, August 06, 2011	Saturday, August 06, 2011
6	Agence One, 30 July 2013, 209 words, (English)	#VALUE!	Tuesday, July 30, 2013
7	The B Online, 19:18, 29 July 2001, 6062 words, (English)	Sunday, July 29, 2001	Sunday, July 29, 2001
8	Green Mail, 29 July 2003, 363 words, (English)	#VALUE!	Tuesday, July 29, 2003
Sheet1

 

[liiamra]

Hey Jonmo,
Thanks++

My bad, I was trying to get another text string work the same way. I just removed the *2 as it is the first comma and it works except for the year.


The difference is the date format. One uses "Day Month Year", while the other uses "Month Day, Year"

Now I see that the year is not identified as it comes after the second comma.


This is the initial:

The Bandi Rock, 08:17, 6 August 1999, 558 words, (English)

This is the other one:

independent Thursday, August 22, 2013 2:46:00 PM CEST | info [other]

Again, many thanks Jonmo and all the best//