Leading Zeros To Specific Fields

Hi,

I am having a problem with excel. I have a program that exports data to my clipboard and then pasted into excel. The problem that comes up is that certain time fields will export as ":19:20". This is not a valid time format for excel. Is there a way to add zero's in front of these specific fields so that the result is "0:19:20".

I need to perform average and sum functions on all these fields. I do not know how to use macros or do any vba, but I am able to follow step-by-step instructions on how to use these two options if it is the only way to correct my problem.

Thanks

Originally Posted by oldchippy

Hi,

If all these values are in column A, then

=("00"&A1)*1

This formula worked out nicely.

Just wanted to ask this because I am curious and want to understand this formula, why the "*1" at the end. I noticed that if I took this out, fields will get changed to a non-time format.