please help
 

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

please help
 

"Brian" wrote in message
...
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.
--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/

please help
 

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
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560131

please help
 

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
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560131