Stripping out unwanted data

**mpb1955** · 06-14-2008, 02:58 PM

I have a csv file of dtata that I can open in Excell.

The data is timestamped in coloumn D and is taken every two minutes.

I.e. the format shown in "D" is 01/01/2008 06:00:00

I need to strip out all the data apart from the ones on the hour.

One months data is 22141 rows so would take ages manually.

I tried to use the filter and put ends in 00:00 but everything dissapears.

Any help appreciated

**VBA Noob** · 06-14-2008, 03:10 PM

Try

Text to columns > delimiter would be space or

=TIME(HOUR(D1),MINUTE(D1),SECOND(D1))

Format cell as time

or

=TEXT(D1,"HH:MM")

VBA Noob

**martindwilson** · 06-14-2008, 03:42 PM

i think mpb1955 means strip the non whole hours out from say
01/01/2008 06:00:00
01/01/2008 06:02:00
01/01/2008 06:00:09
01/01/2008 06:00:07
01/01/2008 06:00:03
01/01/2008 07:00:00

to leave just
01/01/2008 06:00:00
01/01/2008 07:00:00
if thats the case and the cells havent been formatted as time when it was imported, try in an empty column say AA

Please Login or Register  to view this content.

and drag fill down to last row
then copy AA and paste it back special /values into same column(AA)
then sort whole sheet by column AA
that should bring all the full hour ones to top
delete everything after them

**mpb1955** · 06-15-2008, 03:25 AM

martindwilson

Your description of my needs are correct.

I tried the code you posted and it does not work.

The "D" column has been formatted to a date & time and seems to be automatic when opening the original csv file.

dd/mm/yyyy hh.mm

Your code does not bring any values to sort by.

Any help appreciated.