Date and time parsing from text string

Hi everybody,
I have read plenty of entries and other threads but I cannot get the required result. Hence, I am hoping that you might be able to assist me.
Here is the problem statement:
- I have a text field which has no defined pattern and I need to extract dates and times, if possible
- The text field can contain two dates or two times, but not both
- Neither information is at a fixed location within the text field
- The formatting of the dates can vary. There might be other instances of the dash "-" in the cell and thus, it cannot be used as a differentiator

• "dd.mm.yy" - "dd.mm.yy"
• "dd.mm.yy - dd.mm.yyyy"
• "dd.mm.yyyy - dd.mm.yy"
• "dd.mm.yyyy - dd.mm.yyyy"

- The times are in in a uniform 24h format. It appears that the colon ":" is used only in combination with date fields, but similar to the date field, the dash "-" has multiple occurences within the same cell.

• "mm:hh - mm:hh"

Preferrably the results should be in a format such as:
Date start | Date end | Time start | Time end

I tried to search for a character pattern and start extracting from there, but it seems this exceeds my Excel horizon by miles.

I am gratful for any assitance I can get.

Thanks very much in advance.
Max

Hi Norie,
thanks for your respose. I attached a sample file hereto which I hope shows my intentions.
The data originates from a freetext field in a database. So the patterns where the dates might be will be pretty random.

Best regards
Max

Hi José,
thanks very much for your prompt response. Unfortunately, the date and time information is embedded in a random text string.

Best regards
Max

Hi

My first approach
Try in D2, E2, F2 and G2

Formula:

Please Login or Register  to view this content.

Last row has no equal sign and gives a error . I try another alternative

Hi José,
thanks again ! This looks pretty good.
The cell might have entries such as:

"Service Asset 1 & Asset 2 <br />Duration 20.01.2019 - 24.01.2019"

So, yes, there might not be a "=" sign indicating that there might be a date field. Initially I was hoping to be able to parse the cell for a text string ??.??.???? and extract therefrom, but I did not get very far with it ...

Max

Hi

A more robust search date in string In D2, E2. F2 and G2 are the same.

Formula:

Please Login or Register  to view this content.

Note: I use Columns($A$1:$CX$1) which means a search in a string of length 112 (102+10) char. If your string are longer than that use a greater column such as $ZZ$1 (702+10).

See the file

Hi José,
great, thank you very much thus far. I am not able to test it right now on the real dataset, but will do so tomorrow and update you as soon as possible.
Thanks again for your efforts.

Regards
Max

Hi José,
I tested your solution and it works partially. The file holds various formats of times and dates.
Is there a way to look for a pattern in the cell, such as "??.??.??". Maybe that can be an approach rather than relating it to certain characters within the cell ?

Thanks
Max

Hi José,
I re-worked some of the formulas and got a good result to extract the times, but the problem remains with the dates.
Here are some sample entries where your approach does not work. I suppose it is because of the missing CHAR(160).

• Asset 1 Standby<br />Service date/time 05.10.15 00:00 - 06.10.15 06:00
• Service date 23.03.2015 - 27.03.2015
• Asset 1<br />Location 2<br />Service date 05.05.2016 - 06.05.2016

In summary, the formula would need to work for the following date formats:

• [dd.mm.yy - dd.mm.yy]
• [dd.mm.yy - dd.mm.yyyy]
• [dd.mm.yyyy - dd.mm.yy]
• [dd.mm.yyyy - dd.mm.yyyy]
• [dd.mm.yy hh:mm - dd.mm.yy hh:mm]

I tried to serach for pattern, i.e. SEARCH("??.??.??"; ....) but that got me too many mixed results. It would helo, if I were able to do this =SEARCH("##.##.##"; ...), but I understood from other threads looking for numbers patterns with SEARCH is not possible.

Any chance you have another approach ?

Thanks
Max

Try this 4 formulas

Formula:

Please Login or Register  to view this content.

See the file

Hi @MaximH

In my attempts I try to use the SEARCH function in the bad way, with {10,8} array.

Now, I get the same results with this two formulas for dates

Formula:

Please Login or Register  to view this content.

Thank you very much José.
That did the trick. I had to correct a few entried manually, but that was due mainly to the quality of entry, rather than the formula.

Thanks again for your expertise in this case !

Best regards
Max

You are welcome and thanks for the feed-back. I'm happy to have helped.