Milliseconds missing leading zeroes

Hello,

I have a TXT files which has timestamps with milliseconds. The problem is that all values which are less than three digits show up without leading zeroes. So, for example:

2019/9/24, 15:02:08.8
2019/9/24, 15:02:08.9
2019/9/24, 15:02:08.10
2019/9/24, 15:02:08.11

When I try to load the data in excel using h:mm:ss.000 it just appends zeroes to the digits, which is wrong. The problem is compounded by the fact that I also have millisecondd values later on with three digits (i.e. 2019/9/24, 15:02:08.100, 2019/9/24, 15:02:08.101, etc).

Is there a way for me to load the data and fix the column so that all millisecond values show up correctly?

Thanks in advance!

Hi,

Thanks for the quick reply! Anything with 3 digits would keep those three digits. So I guess I'm wondering whether excel can identify where there are only one or two digits, and then add one or two leading zeroes. Not possible?

Alternatively, is there a way I can get excel to just load the numbers as they are?

Originally Posted by AliGW

... and the resulting data will be seen as text by Excel, not as a date and time ...

Ali,

couldn't you multiply your formula by 1 to convert it back to date/time format?

Pete

Wow, this is great. I'm so excited to try this out, but... Now I have a different problem being able to implement your solution. When I format the cells as text, in order to get the original timestamps, they should up as a number like

0.626489583
0.626490741
0.626491898

I've no idea why!? Any idea how I can get excel to show the original (erroneous) timestamps?

Thanks so much for all your help, and on a Sunday no less!!!

My problem is that when I try to load my txt file (data>from Text/CSV) it automatically load the data with a format which doesn't show
the original numbers for that column. If I try to change the formatting of that column to ss.00 or ss.000, it just appends trailing zeroes,
and if I do ss.0 or ss.00 it also cuts of the third digit of those numbers that have three digits.

Also, the date and time are automatically loaded into two separate columns (which is how I need them).

I'm attaching a snippet of the file for your perusal. Thanks so much!

I think what the OP is saying is that whatever produces the .txt file is doing it wrong. The first few time values in that file should be 15:02:08.007, 15:02:08.008, 15:02:08.009, 15:02:08.010, 15:02:08.012, i.e. for some reason the software that produces the .txt file is removing any leading zeros from the milliseconds part of the time field.

Pete

Pete, that's exactly it!