Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|