Excel formula to extract date from large text field

[djredden73]

I have a set of data where a large text field contains a date after a specific set of words.

Example:
In

Text Field	Date (Formula)
Here is where the large text field is and there will be a lot of text in here. Around this point of the text (but not always exact position) there will be this phrase: Released Date: 30.05.2008 (this is the date format) I would like to have an excel formula in the next cell that extracts 30.05.2008 - and preferably convert to 5/30/2008	5/30/2008

<tbody>
</tbody>

Please help with a formula that can extract that for me.

Thank you

 

[Rick Rothstein]

Extracting it is easy...

=MID(A1,FIND("Released Date: ",A1)+15,10)

 

[Rick Rothstein]

Extracting it is easy...

=MID(A1,FIND("Released Date: ",A1)+15,10)

And here is the formula to retrieve it as an actual date...

=DATE(MID(A1,FIND("Released Date: ",A1)+21,4),MID(A1,FIND("Released Date: ",A1)+18,2),MID(A1,FIND("Released Date: ",A1)+15,2))

 

[djredden73]

Thank you.
I was mistaken. Just looked at my data and it doesn't even stay as consistent as Released Date.
The formula you gave worked when it was.
Is there a way to pull it if the format of the date is consistent?
The date format is always DD.MM.YYYY

 

[Rick Rothstein]

Thank you.
I was mistaken. Just looked at my data and it doesn't even stay as consistent as Released Date.
The formula you gave worked when it was.
Is there a way to pull it if the format of the date is consistent?
The date format is always DD.MM.YYYY

While not 100% guaranteed (something like AB.CD.EFGH will fool it), this should probably work for the text you seem to indicate you will have to parse...

Extract As Text: =MID(A1,SEARCH("??.??.????",A1),10)

Extract As Date: =DATE(MID(A1,SEARCH("??.??.????",A1)+6,4),MID(A1,SEARCH("??.??.????",A1)+3,2),MID(A1,SEARCH("??.??.????",A1),2))

 

[Francisco2888]

While not 100% guaranteed (something like AB.CD.EFGH will fool it), this should probably work for the text you seem to indicate you will have to parse...

Extract As Text: =MID(A1,SEARCH("??.??.????",A1),10)

Extract As Date: =DATE(MID(A1,SEARCH("??.??.????",A1)+6,4),MID(A1,SEARCH("??.??.????",A1)+3,2),MID(A1,SEARCH("??.??.????",A1),2))

Hello!

It's been very useful for me this formula, thank you. I just wonder how can I make it work when I have two different date formats:

27-ENE-2014 --> ??-???-????
2-FEB-2014 --> ?-???-????

and two different formats before the date:

Semana 5 (27-ENE-2014 al 2-FEB-2014) --> 10 ch. before the first date
Semana 10 (3-MAR-2014 al 10-MAR-2014) --> 11 ch. before the first date

 

[Luigi802]

Extracting it is easy...

=MID(A1,FIND("Released Date: ",A1)+15,10)

I don't understand, what is "Released Date: "? I typed it in exactly as you have it here and it gives me a #VALUE ! error

 

[Rick Rothstein]

I don't understand, what is "Released Date: "? I typed it in exactly as you have it here and it gives me a #VALUE ! error

The OP said this in his first message...

Here is where the large text field is and there will be a lot
of text in here.
Around this point of the text (but not always exact position) there will be this phrase:
Released Date: 30.05.2008 (this is the date format)

I read this as meaning the date the OP wanted appeared after the phrase "Released Date:"... if you do not have that text appearing before your date, then the formula will not work for you. However, if your date is preceded by some other fixed text phrase, then you can use it in place of the "Released Date:" phrase in my formula.

 

[Luigi802]

The OP said this in his first message...

I read this as meaning the date the OP wanted appeared after the phrase "Released Date:"... if you do not have that text appearing before your date, then the formula will not work for you. However, if your date is preceded by some other fixed text phrase, then you can use it in place of the "Released Date:" phrase in my formula.

Gotcha, I have a list of dates like the following:

September 28, 2017 at 07:00PM
October 3, 2017 at 07:20PM
November 5, 2017 at 12:48PM

I want to put the date in 1 cell and the time in another, but formatted as time/date

 

[Rick Rothstein]

Gotcha, I have a list of dates like the following:

September 28, 2017 at 07:00PM
October 3, 2017 at 07:20PM
November 5, 2017 at 12:48PM

I want to put the date in 1 cell and the time in another, but formatted as time/date

For those date/time text values...

The date: =0+LEFT(A1,SEARCH(" at",A1))

The time: =0+RIGHT(REPLACE(A1,LEN(A1)-1,0," "),7)

These formulas will return date and time serial numbers so you will need to format their cells with the desired date and time formats that you want them displayed as.