Get Dates and No.s from String - any format and multiple dates

[prasaddn]

Hi All,

This situation of mine may sound more like natural language processing... the simplest would be to fix the format at (root level) who creates this text. Unfortunately, that is not in my control. so i have to find my workaround. I am sure with such huge excel knowledge base resource here this can be achieved thru formulas or VBA.

Well, I have texts (lets say in Col A) that consists of information like date/s and number of hours they were on leave. I need to extract Date in Col B, and no of hours in Col C. And, for each additional date, it should get extracted in subsequent column along with hours.

i have tried the many formulaes and codes, including the links in Chandoo..
http://chandoo.org/wp/2012/08/17/extract-dates-from-text/
http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/

but they all work only for few scenarios and i am failing to find one function or formula that can cover all the scenarios.

Attached is the excel sample file.
Note: Col A is given text, and Col B and C and subsequent columns are expected output..

any help will be highly appreciated..

Regards,
Prasad DN

 

[prasaddn]

Thought of sharing the detail on screen, just in case any of you have issue opening the excel attachment:
Given Text
08/18/2017 8 hours
expected output:
8/18/2017 and 8:00:00 in two columns

Given Text
08/16/2017 7 hours 10 minutes
8/16/2017 and 7:10:00 in two columns


Given TExt
7.27.2017 -8 hrs, 8.3.2017 8 hours, 8.14.2017 8hrs
Expected output
07/27/2017 and 8:00:00 and 08/03/2017 and 8:00:00 and 08/14/2017 and 8:00:00 in each column.


for more scenarios check the attachment..

 

[Chihiro]

What you describe is pretty difficult task and will require writing patterns for each variation that could occur in your list.

Normally, it is far easier to ensure proper patterns are used at the time of data entry. And it is recommended strict rules are enforced for date/time value entry.

If this cannot be enforced for some reason, you'd need to construct list of all possible patterns that could be used. Which will be time consuming and prone to error and/or missed pattern (especially when user uses their own short form for month etc).

I'd highly recommend enforcing validation at data entry point.

 

[SirJB7]

Hi, prasaddn!
No way of doing it without all pattern masks previously defined.
Regards!

 

[prasaddn]

Hi All,

Wish I could have found some way.. Let me try if I can put validation in the source area itself. It is going to be a tough task... :s

Regards,
Prasad DN