extract time-values from variable text

Hereby the cell:

"From 07/09/11 till 13/06/12 Ma Gesloten Di Gesloten Wo Open from 14:00 till 16:55 Do Gesloten Vr Gesloten Za Gesloten Zo Gesloten Meer uitleg : Geen lessen tijdens de schoolvakanties "

I need to create a macro/script to extract 14:00 and 16:55 in above text, these are two timevalues (from and till). They need to be extracted each to a new cell in the same format HH:MM. Text is variable.
I have a variable column with values, so the macro/script needs to applie to a whole colum (range x:x)
Please advise!
Thx!

Hello,

is the text all in one cell? If so, you can extract the start time with

=TIMEVALUE(MID(A1,FIND(":",A1)-2,5))

and the end time with

=TIMEVALUE(MID(A1,FIND(":",A1,FIND(":",A1)+1)-2,5))

In case the text does not contain two time values, you would need to use error trapping with IFERROR() or similar.

cheers,

Thank you for the quick follow-up.
Yes, all of it is in one cell.
Your code produces an error?
Can you explain a bit further about the error trapping? And how do i put this in a macro/function in VB?
Thank you.

Your code produces an error?

Can you specify that?

See attached.

Why would you want to use VBA if a formula does it nicely?

Hi William,

I keep trying to find some fault with teylyn's formulas and rarely (as in never) do. You will need to adjust her example formulas by changing A1 to the cell you have your text in. Her formula looks for the first colon and second colon in the text and uses them to find the start and end times. If you were to have a colon before them, her formula won't work.

If you supply a sample workbook with what doesn't work, I'm sure we (mostly teylyn) can find exactly why.

Your code produces an error?
Can you explain a bit further about the error trapping?

On behalf of Teylyn
If there are no time values to be found in your string then the formula will return a #VALUE! error.
Given that you are using 2003 then the error trapping is handled like this:=

=IF(ISERROR("formula"),"","formula")
or to avoid blank cells returning an error
=IF(A2="","","formula")

[EDIT]
To address Marvins' concerns about colons preceding the time I've added a formula to minimise the risk of errors
e.g.

Please Login or Register  to view this content.

Attachment updated

You might need to change the comma "," seperators to semi-colons ";" in the formulae.

In excel we use VBA.

I think you are using a flemish version of Excel ?
to split the time in cell A1 to cell B1 and C1:

Please Login or Register  to view this content.