Extract the time from a text string

[knighttrader]

I need to extract the time from various text strings, example below:

"Killar 16th Jul - 18:15 2m4f Hcap Chs"

The problem is that the length of the text before and after the time vary. The only constant is the "- " before the time.

Also, I need the result to be in time format, hh:mm.

Many thanks

[Richard Schollar]

Hi

Assuming that data in A1 then try:

=MID(A1,FIND("- ",A1)+2,5)+0

Format the resulting formula cell as

hh:mm

to display it in your desired format.

Richard

[Rubeus_Hagrid]

Hi,

Instead of using find, use search function.

e.g. (assuming data is in cell A1)

=MID(A1,SEARCH("-",A1)+1,6)

[Richard Schollar]

OK you've piqued my interest - why would you use SEARCH over FIND?

[rasonline]

find is case sensitive, search is not.

in this instance it shouldn't make a difference

[shg]

I'd expect that FIND is slightly faster for that reason.