problem with replace

[gloum]

Hello. I have a column with minute intervals such as this
05:00
05:05
05:10
05:15
05:20
05:25
05:30
05:35
05:40
05:45
05:50
05:55
06:00
06:05 etc

I want to replace all the cells beginning with 05 with a number (for example 5) and all the cells beginning with 06 with another number (for example 6). The problem is that if i replace '05:' with '5' then '06:05' is also replaced. I do not want to do it manually because i am looking at an excel file with more than 30.000 rows. What do i have to do in order to replace them?The same problem occurs when i try to replace all the 10:somethings (and the 15:somethings and 20:something, has to do with the fact that 5,10,15,20 appear in minutes as well). How do i work around this problem? Thanks

 

[Scott Huish]

=IF(HOUR(A1)=5,A1+"1:00",A1)

Copy down.

Copy this entire column. Paste Special Values on the original column. Delete the column with the formula.

 

[gloum]

All i got are values like these


0.25
0.253472222
0.256944444
0.260416667
0.263888889
0.267361111
0.270833333
0.274305556
0.277777778
0.28125
0.284722222
0.288194444
0.25
0.253472222


For every solid hour (5:00, 6:00 and so on) i get the value 0.25.. Cannot say that solves the problem though..

 

[Scott Huish]

Format as h:mm

 

[gloum]

Saw that a bit late..Solved my problem with replace before that.Thanks