Problems with text to columns/time formatting

[carrielovesfanta]

Hi all,

I'm hoping you can help me. I've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:

hh: | mm: | ss

I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM

I've posted my code below - my apologies if this is on the wrong forum etc

Please Login or Register  to view this content.

Thanks very much!

Caz

[JBeaucaire]

You could use formulas to draw out the same values without having to fiddle with the original cells:

=HOUR(A1)
=MINUTE(A1)
=SECOND(A1)

Then copy/pastevalues to remove the formulas. That could be made into a macro as well.

[carrielovesfanta]

You could use formulas to draw out the same values without having to fiddle with the original cells:

=HOUR(A1)
=MINUTE(A1)
=SECOND(A1)

Then copy/pastevalues to remove the formulas. That could made into a macro as well.

Hi, thanks for the reply - I think you're right in that I'm going to have to appraoch this from a different angle. I'll use the hour, min, sec formulae you suggested.

Thanks again!

Caz