Change time format from :0 to 0:0

[Guest]

I have tons of times in this format:
:42:01
:02:58
:04:45
:25:13

and need to change to:

0:42:01
0:02:58
0:04:45
0:25:13

Pls help...
tia
Donald

 

[Guest]

=TIME(0,MID(A1,2,2),RIGHT(A1,2))

fomat cells as h:mm:ss
copy down

 

[Sandy Mann]

:42:01

:02:58
:04:45
:25:13

are all Text not times try using this formula in a newly inserted column:

=("0:"&(SUBSTITUTE(J1,":","",1)))+0

then copy and paste the column back as Paste Special > values and reformat
the column as time then delete the original column.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thank you both... both formulas work great!

are all Text not times try using this formula in a newly inserted column:

=("0:"&(SUBSTITUTE(J1,":","",1)))+0

then copy and paste the column back as Paste Special > values and reformat
the column as time then delete the original column.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Harlan Grove]

I have tons of times in this format:
:42:01
:02:58
:04:45
:25:13

and need to change to:

0:42:01
0:02:58
0:04:45
0:25:13

Are these numbers formatted as ":mm:ss"? If so, change the number format to
"[h]:mm:ss". If they're text, and you want to leave them text, use

="0"&x

where x is the address of a cell containing one of these times. If they're
text and you want to convert them to values, use the formula

=--("0"&x)

and format appropriately.