Excel Formula

[MattR]

For something like this....

HGrn 7th Nov - 12:32 A7 480m

in a cell, where the first part (in this case a track name) will not always be the same length. Is there a way to extract the race time from it (ie the 12:32 part)

Is this possible due to the changing length of the string if at a different track, date with 2 numbers etc. I wonder whether you'd have to first find the consistent thing, say the "-" and then find two on from it for the next five characters to extract the time.

[mathare]

For something like this....

HGrn 7th Nov - 12:32 A7 480m

in a cell, where the first part (in this case a track name) will not always be the same length. Is there a way to extract the race time from it (ie the 12:32 part)

Is this possible due to the changing length of the string if at a different track, date with 2 numbers etc. I wonder whether you'd have to first find the consistent thing, say the "-" and then find two on from it for the next five characters to extract the time.

It can definitely be done even if the string length varies as long as the format remains constant. The only thing is it can only really be done in VBA rather than as an in-cell formula. Or rather I only know how to do it in VBA. But you could use a User-Defined Function (UDF) to do it, if you're happy to have VBA enabled within the Excel file of course.

Let me know if you're OK with VBA and then we can go from there.

But basically you need to start by finding the : in the time part of the string and then look left and right for spaces and use those to identify which section of the string you want to extract. You can do it other ways, such as finding the : and then checking all characters around it until you find something that isn't 0-9, it depends on how robust you need it to be which is based on how often that string format will change.

[MattR]

Thanks Mat.

I've just thought of an easier way I can get what I need here. Can I display the time in that format hr:min without seconds? I tried the NOW function and get the date/time which would also work.

So, if I can just display the current time as (12:32) etc, what's the formula for that?

Or if not then what would be the formula to extract just the hr/mins from the NOW output?

[mathare]

Thanks Mat.

I've just thought of an easier way I can get what I need here. Can I display the time in that format hr:min without seconds? I tried the NOW function and get the date/time which would also work.

So, if I can just display the current time as (12:32) etc, what's the formula for that?

Or if not then what would be the formula to extract just the hr/mins from the NOW output?

Do you need to extract the time from the NOW output or just display it as hh:mm? If the latter is OK then right-click on the cells that will display the time, select Format Cell, select Custom from the left-hand side (Category) and enter hh:mm in the Type field on the right-hand side

[MattR]

Hmm, that works fine but there must be something it doesn't like with the format.

If I explain a bit on the speadsheet that may help. Basically other cells are looking at this one that is to have the time in and it's using that to look up a vlookup table that has the race time as the first column. Now, when I type in a time into the cell such as 12:32 it works fine and picks out the required items from the vlookup table. But when I use the NOW function in the hh:mm format it just takes the last row in the vlookup table instead even though that isn't matching the time. Also if I change one to match the current time it isn't picking it from the table, yet if I manually type in the time it does.

[mathare]

If I explain a bit on the speadsheet that may help. Basically other cells are looking at this one that is to have the time in and it's using that to look up a vlookup table that has the race time as the first column. Now, when I type in a time into the cell such as 12:32 it works fine and picks out the required items from the vlookup table. But when I use the NOW function in the hh:mm format it just takes the last row in the vlookup table instead even though that isn't matching the time. Also if I change one to match the current time it isn't picking it from the table, yet if I manually type in the time it does.

What I got you to change was just the display format - the NOW cells still contain the full time/date string but it's now only showing you the time so if you're asking if one of the NOW cells is 12:32 it'll always come back false because it contains the date and more accurate time information.

So in answer to my earlier question, yes, you need to extract the hh:mm information rather than just change the display options. Which puts us back into UDF territory I think. But then VBA is my answer to everything I can do quickly and easily with the standard formulae.

Want help with the UDF? (which I guess really means do you want me to write it)

[MattR]

Yes, looks like that's the only way then, to extract the hr:mm from the NOW function then. As to the help, yes please if you don't mind! :D

[mathare]

Can you e-mail me a copy of the workbook so I can see the format and stuff in context and hence get the code right? You never know, if I look at it I may even spot a different way to get the same end result you're after but without resorting to UDFs

[MattR]

Will do Mat. Thanks