Instr function that uses various characters as 1

Hi all

I am trying to write a function that recalculates given times to points (as used in speedskating)
The times are filled in by a number of people, which each use their own methode of dviding minutes from seconds (and 1/100 of a sec)

I found out how to see the difference between times that look like 1.11.11 and 11.11 etc

What I can't figure out is how to use that same method in an InStr function. Most of the times I get an error message #Value or something that has that format, sometimes all I get is a 0

I have made a demo that hopefully makes better sense than this rather clumpsy description

Is there a way to make this work?

All help appreciated

Hein

Hi,

I don't understand what you are trying to do and what your various values represent. Neither can I see what 'TimeToPoints' is other than perhaps an as yet unwritten function.

Would you manually add some example results covering all permutations and explain how you arrive at the results.

I thought you were trying to convert the strings to times, well, the same time in the example.

Try this:

Please Login or Register  to view this content.

I'm sure there are alternatives using Regular Expressions but that is one of my weak areas.

If the string cannot be evaluated as a time (by Excel), you can process it character by character. If Excel recognises it as a valid time, it will be treated as a fractional number so you won't be able to test for specific characters, nor, I guess, do you need to.

Regards, TMS

Regards, TMS

Hi,

Does this one help?

Please Login or Register  to view this content.

Cancel that: misread the requirement

OK, for my benefit anyway, my reworked solution.

Please Login or Register  to view this content.

The only concern that I have is that some of your entries may be interpreted by Excel as a time. If that is the case, the content of the cell will be a numeric value representing a fraction of a day. You may not get the value you expect under those circumstances.


Regards, TMS

You're welcome. Thanks for the rep.



Please note that my solution produces different results to Richard's. You may want to review the attached workbook and tweak one of the solutions to better meet your needs.


Regards, TMS



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

A variation on a theme ... something like my original approach but building on the one I listed above:

Please Login or Register  to view this content.

This also copes with times but, if the first digit is 0 it drops it. So, 9:56:25, which may be interpreted as 09:56:25, will return 9, rather than 0 or 09. 19:56:25 will return 19:56:25 will return 19 regardless of whether it is seen as a string or a time.


Regards, TMS

No more variations, I'm done