Function to pull date from free-text field.

[ry94080]

Hello,

I'm pulling data from our IS system. There is a free-text field that has some explanation text and also some date and time information.

I was hoping to find some code that would extract the date and time into a separate field to be able to run a calculation against.

The text has been kind of consistant for the most part. Examples:

"blahblah blagbhilaieragnleiobnobhreehb Results called by LV00000 at 23:34 on 11/23/2022."



It's always some random text followed by "called by LV00000 at time on date."

Any ideas on how to pull these data elements out?

[pbaldy]

If it's always at the end, you can use the Right() function if the number of characters is consistent (01/01/2023 rather than 1/1/2023). If not, you can use the Mid() function along with InStrRev() to find the last space.

[ry94080]

It's consistent for the most part but not all the time. I think it has to be some logic where it looks for "/"'s and pulls the date from there.

Any ideas?

[pbaldy]

This might be of interest:

https://www.consultdmw.com/extract-d...xt-string.html

[Gicu]

Paul already gave you the answer with the InStrRev, have you tried it?

[xps35]

Try CDate(Mid([F1],InStrRev([F1],"/")-5,10))

(replace F1 with your fieldname)

[Gicu]

Here is an example using Paul's original suggestion and another using the Split function.

Cheers,