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

 

[Luigi802]

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.

AWESOME! Thank you very much, that worked perfectly@

 

[aesterling]

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.

This is great, thank you for sharing! In my case, the date formula that you provided worked perfectly to extra the data, but my source times are formatted a bit differently than in Luigi802's examples: (The hour varies between one or two digits, seconds are included followed by a space, and it varies between AM/PM.)

Example:

November 6, 2019 at 8:58:00 AM
August 3, 2018 at 10:53:03 AM
June 4, 2016 at 4:33:05 PM

Is it possible to modify your original time extraction formula to work with the examples above? Thank you in advance!

 

[Rick Rothstein]

This is great, thank you for sharing! In my case, the date formula that you provided worked perfectly to extra the data, but my source times are formatted a bit differently than in Luigi802's examples: (The hour varies between one or two digits, seconds are included followed by a space, and it varies between AM/PM.)

Is it possible to modify your original time extraction formula to work with the examples above? Thank you in advance!

All that was needed was to change the 7 to an 11 to get the time part for your text...

=0+RIGHT(REPLACE(A1,LEN(A1)-1,0," "),11)

 

[aesterling]

All that was needed was to change the 7 to an 11 to get the time part for your text...

=0+RIGHT(REPLACE(A1,LEN(A1)-1,0," "),11)

Awesome, thanks so much! I appreciate your help!