CharAt command in excel

[wilro85]

Is there any "CharAt" command in Excel similar to the "CharAt" function in Java?

I'm currently getting data from a program that doesn't know how to tell time.

Normally, time is given in a hh:mm:ss:## format. So if something happened at 12:08:15:20. The program that I'm using is truncating the leading zero off of values less than 10. So the example time above would display as:
12:8:15:20

What I'm hoping to do is find the best way to go thru and find the location of each ":" so I can break the time up into its components "12" "8" "15" and "20" then put them back together in the correct format.

I've been unable to find a CharAt function, so right now I'm thinking the only way to do this is a bunch of nested If(right(left(<cell>,i),1)=":"....)

Anyone have any ideas?

P.S. I didn't write the program that is giving the bad time values. The company that made that software is working on the problem, but I don't want to sit on my hands waiting for them to fix it.

[AndySuk]

I don't know the CharAt command but you could use Text to Columns from the data menu and use the colon as a delimiter?

Maybe too restrictive as I don't know if you could use it withon a routine but just a thought.

A

[wilro85]

Well, it's not the most eliquent solution... but it's better than what I was doing and it should hold me over until they are able to fix their program.

Thanks!

[shg]

Are there really three colons in the format, or is the last actually a decimal point?

[wilro85]

It's really all colons. Attached is a copy of what the program outputs.

I have a program that takes care of the data, just need to change the time formats to a standard time. (I.E. 12:7:35:20 -> 12:07:35:20)

[shg]

That was my point: hh:mm:ss:## is not a valid format, at least in my version of Excel. hh:mm:ss.ss is.