Filter column with cells with leading zeros

Hi,

This is my problem:
I have log files that I want to import into excel to filter the data and calculate averages for ranges of data, for example between a certain time interval. The log files are text files that are divided into columns by tabs which makes them easy to import into excel. One of the columns is in the following format:
YYYYMMDD_HHMMSS
I want to separate this column into one column for date and one for time. I use the text to columns tool to do this and have to make the time column into text format so I dont loose the leading zeros.

The time column now looks like this (since the log starts at the beginning of the day):
235958
000006
000012
000018
000024
000030
000042

I apply a filter for the sheet but since the values in the time col now are in text format (strings) I cant sort them as numbers (obviously). I want to be able to use the "between" filter, any ideas? I have tried the change the cells to time format but I just get a format like 1900-05-06 00:00 and other similar forms.


I would really appreciate any help!
many thanks
erik

Instead of using text to columns you could use formulas to get the date and time. If the YYYYMMDD_HHMMSS values are in column A you can use

=DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2)) to get the dates and

=TIME(MID(A2,10,2),MID(A2,12,2),MID(A2,14,2)) to get the times.

These can then be filtered.

It works! Thanks a bunch and then some!