BT call details and Excel

  Zak 19:49 19 Nov 2003

I regularly download a .csv file with my quarterly call details from BT.

I wish to confirm the cost of the calls per minute and have set up a spreadsheet for this.

The major problem is that BT format their time duration as 0000:00:00, which means that Excel does not recognise this. The only way I have managed to get round this is to manually remove the two leading zeros from the hours. My formulas then do the rest. No custom formatting has been successful.

I have thought of recording a macro. I would need to convert a whole column of say up to 200 rows. Is there a shorter way by specifying the range? I am not at all conversant with VB, so any guidance would be much appreciated.

I hope the above makes sense.

  Zak 21:39 19 Nov 2003

Refresh hoping VoG is on line.

  VoG II 21:59 19 Nov 2003

Create a "spare" column. Let's say your first value is in B3 and your new column is C. In C3 enter the formula


This will strip the first two leading zeros.

You can then copy the formula down as far as needed.

  Zak 22:25 19 Nov 2003

Many thanks VoG.

Brilliant, it is so simple when you know how. I had searched on and off with Google and not found the answer.

Thanks again for such a quick reply and a successful solution to my question.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HTC U12 Plus review: Hands-on

Best Android emulators for Mac

Comment utiliser l’Apple Store Éducation ?