please help

[Guest]

in column F and about 700+ cells down, are times that where entered like
this...
010754. But the times were meant to be entered as such... 01:07:54. The
colons are missing. Is there a quick and easy way to somehow enter the
colons in all the times, without having to do it manually for each time?

Thanks,
Brian

 

[David Biddulph]

in column F and about 700+ cells down, are times that where entered like
this...
010754. But the times were meant to be entered as such... 01:07:54. The
colons are missing. Is there a quick and easy way to somehow enter the
colons in all the times, without having to do it manually for each time?

=TIME(LEFT(F1,LEN(F1)-4),MID(F1,LEN(F1-3),2),RIGHT(F1,2))

This could be simplified slightly if the number in column F has preserved
its leading zero, but the formula above copes with cases where it hasn't.

 

[Dav]

If your times are text strings, hence the leading 0 and the first cell
you wish to change is f8 in the next colum put
=TIME(LEFT(f8,2),MID(f8,3,2),RIGHT(f8,2)) and copy this doen to all the
rows that are effected. If you then select all of them and copy then
past special as values on top of themselves and then copy these values
ontop of the text strings this should sort you out. You can then delete
the extra column

Regards

DAv

 

[Dav]

If your times are text strings, hence the leading 0 and the first cel
you wish to change is f8 in the next colum pu
=TIME(LEFT(f8,2),MID(f8,3,2),RIGHT(f8,2)) and copy this doen to all th
rows that are effected. If you then select all of them and copy the
past special as values on top of themselves and then copy these values
ontop of the text strings this should sort you out. You can then delet
the extra column

Regards

Da