Adding a Prefix 0 to column using macros

**dvega1819** · 08-15-2012, 08:21 PM

Hey Guys, I'm stumped.

Hey Guys,

I have this program which I'm attempting to export to excel but the values, i.e. (hh:mm [2:15], [:50]), can only come through only as text format so that the values don't change even though the format is the same. I attempted to change the formats but unfortunately, whatever was imported stay as text. I found out that if I add a "0" in front of these entries and change the format to [hh:mm] it can work but 500+ files are to many to change manually so I entered the following macro but it only changes the first cell. If anybody can revise the following code so that the range is from e1:e700.

Thank You,

Please Login or Register  to view this content.

**macropod** · 08-15-2012, 08:48 PM

The first thing I'd suggest you do is to select the affected range then use Excel's cell formatting to change the format to 'Time'. Then, in any used cell, insert the number '1'. Copy the cell then re-selct your 'time' range and use Paste Special > Values > Multiply. That should be sufficient to convert any 'text' times to formatted timevalues.

**dvega1819** · 08-16-2012, 10:39 AM

Thanks Macropod,

The only thing is that there are some cells that got exported but since they didn't have any hours they were exported as, i.e. (:50). The formatting that you gave me turns the ones with hours, i.e. (1:50) into actual time but since the ones that don't have hours don't have a zero in front of it, I guess it still doesn't recognize it as time. Which is why I was looking into macros to automatically add a prefix of a zero and the code that i have works but for some reason only does the 1st row.

Thank You

**macropod** · 08-16-2012, 06:34 PM

That suggests the affected cells are only formatted as ':ss' or ':mm'. You should format all of them the same way, ie: 'hh:mm:ss', or 'hh:mm' or 'mm:ss'.