Stripping out unwanted data

[mpb1955]

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]

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]

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

=IF(ISERROR(FIND(":00:00",D1,1)=14),"",D1)

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]

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.

[VBA Noob]

Maybe

=RIGHT(TEXT(A1,"H:MM"),2)="00"

Change A1 to the start of your data and drag down. Filter on True to only show whole hrs

VBA Noob

[mpb1955]

VBA Noob

Great solution

Thanks