Macro Search+Replace Error

**DoodleExcel** · 05-26-2010, 04:44 PM

Hi everyone, I used the record button in excel 2003 create a macro that will replace all the" 03:" in a selection into "00:03:", the same with 2,4,5,6 and then remove the last ":00" (the format is xx:xx:00).
Because the time was seen as hours instead of minutes.
I used this code:

Please Login or Register  to view this content.

Eg: 03:16:00 should become 00:03:16.
But it doesn't do anything, how so?
Sometimes it even adds AM to the end.
Thank you very much in advance.

**davegugg** · 05-26-2010, 04:52 PM

Perhaps your problem has more to do with the cell formatting than the values. Try highlighting those cells, then right click and go to Format Cells... On the Number tab, pick an appropriate type for how you want the data to look.

**DoodleExcel** · 05-27-2010, 10:50 AM

Thank you for the quick reply, my formatting for these cells are :

Please Login or Register  to view this content.

I copy data from iTunes, and then use formulas to tell me information about it, but in excel the formatting is eg. 03:16:00, the first part being the minutes, then seconds, and then 'don't ask me why) but micro seconds. If you could show me how to do a better formatting it would also be great

Oh yeah, i'm not sure if the last :00 are already there in iTunes or if excel just adds them

**davegugg** · 05-27-2010, 12:04 PM

Use the Custom number format:

Please Login or Register  to view this content.

This is in the Custom list included with excel.
Isn't that the format you are looking for?

**DoodleExcel** · 05-27-2010, 12:21 PM

That solves the problem if I use the format

Please Login or Register  to view this content.

(because the :00 is still at the end), thank you.
But then my formulas are screwed up since it should be in minutes.
Can you create a custom format like hh:mm and choose when it "changes" like after 60 minutes you have 1 hour?
But instead be seconds.