BT call details and Excel

  Zak 19:49 19 Nov 2003
Locked

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

=RIGHT(B3,8)

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.

Zak

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 ?