How do I get rid of milliseconds from a whole column of data in Excel?

Kim Walker September 3, 2012
Pinterest Stumbleupon Whatsapp

I have been trying and trying to get milliseconds removed from a time stamp in a column of data and I can’t do it. The time is in military time that looks like this:

12:32:01:.6240
12:32:01:.6339
12:32:01:.7249
12:32:02:.7259

And there is another column in another spreadsheet that I need to compare the time stamps to, but it does not have the milliseconds. It just looks like this:

8:00:51
8:00:51
9:02:06
9:02:06
9:20:26

I have tried writing a macro, everything I know how to do, but I can’t get it to change the whole column. It puts PM in the formula bar. It only changes one at a time. And there is 23K+ rows of data, so I can’t do it one by one. Please help!

Thanks,
Kim

Ads by Google

  1. Paul Pruitt
    December 11, 2012 at 1:47 am

    1. Highlight the column with the milliseconds in it.
    2. Hit Ctl-H and replace all instances of ":." with some random obscure character, say the tilde "~" or even the "@" sign.
    3. Select Text to Columns in the Data menu.
    4. Make sure you have the radio button on "Delimited" in the next screen.
    5. After hitting next, choose "Other" for your Delimiters and put in your random character.
    6. On the next screen click on the column with the now separated milliseconds and move the radio button to "Do not import column(skip)" to change the column header from general to that phrase.
    7. Hit "Finish".

  2. Vipul Jain
    September 4, 2012 at 3:39 pm

    just select the cells and format them under time and remove the miliseconds

  3. Wael Guirguis
    September 3, 2012 at 8:45 am

    If you supposed that first column A contains
    12:32:01:.6240
    12:32:01:.6339
    12:32:01:.7249
    12:32:02:.7259
    so you may use the following formula at any other column
    =LEFT(A1,FIND(".",A1,1)-2)
    to get the following result:

    12:32:01
    12:32:01
    12:32:01
    12:32:02

    I think now you can compare the two columns..

    • Bruce Epper
      September 3, 2012 at 7:54 pm

      If you use the @NOW function, you will find that there is not a colon after the seconds, so your formula will need to be changed to=LEFT(A1,FIND(".",A1,1)-1)to get the desired results.

  4. Bruce Epper
    September 3, 2012 at 5:42 am

    Right-click on the column header that contains the timestamps (F for example). Select 'Format cells...' from the context menu. On the General tab, select the 'Time' category, then the '13:30:55' format specifier. This will display every data item in that column in HH:MM:SS format without any milliseconds. Click on OK to make the change. Or you could use the 'Custom' category and specify 'hh:mm:ss' for the format (to get the milliseconds you would need to use 'hh:mm:ss.0')

    • Rajaa Chowdhury
      September 3, 2012 at 12:07 pm

      It will still maintain the milliseconds value, though suppressed to the naked eye. Sounds funny, but I do resolve this problem a lot by using the text to column wizard and and select fixed width, click the point from where I want the data to be removed and that's about it. Just did it with the sample data you have given, and it worked perfectly. Try it.

Ads by Google