Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings everyone!
I have an issue to solve, but I just can't figure out how to do it...
I must extract a StartDate / FinalDate, and StartTime / EndTime from the following string:
''From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00''
Any ideas?
Thank you all one more time!
Try this:
Startdate: =subfield(subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', ': ', 2),' ',1)
FinalDate: =subfield(subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', ': ', 3),' ',1)
StartTime: =subfield(subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', 'and ', 1),'en: ', 2)
EndTime: =subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', 'and ', 2)
Regards
Could you post some sample data as this should be quite straight forward assuming the data is fairly uniform.
All the best,
Matt - Visual Analytics Ltd
Hi,
there are the string functions: Left, Right and Mid, it could look like this:
Load ...,
Date(Mid('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00',7,10),'D/M/Y') As DateStart,
Date(Mid('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00',21,10),'D/M/Y') As DateEnd
Time(Mid('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00',40,5),'hh:mm') As TimeStart,
Time(Right('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00',5),'hh:mm') As TimeEnd,
...
From ... ;
Your only Problem is, that you have in Oct, Nov and dec 2 numbers for the month.
Try this:
Startdate: =subfield(subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', ': ', 2),' ',1)
FinalDate: =subfield(subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', ': ', 3),' ',1)
StartTime: =subfield(subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', 'and ', 1),'en: ', 2)
EndTime: =subfield('From: 18/4/2011 to: 22/4/2011 Between: 08:00 and 18:00', 'and ', 2)
Regards
I would go...
date(mid([Field Name],index([Field Name],' ')+1,mid([Field Name],index([Field Name],' ',2)-1),'D/M/YYYY') as 'StartDate'
And do the same for the others except use ,3,4 in the index and so on. Probably messed up syntax somewhere or did the date 'D/M/YYYY' thing wrong but hopefully you get the idea.
Thank you very much for all your answers and suggestions!
I have performed a workaround and treated the data in a different way, but all your suggestions worked fine! A combination of them form a perfect solution!
Again, thx for your attention!