BT call details and Excel

  Zak 19:49 19 Nov 03

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 03

Refresh hoping VoG is on line.

  VoG II 21:59 19 Nov 03

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 03

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.

Microsoft Surface PC release date, price and specs: All-in-one Surface PC to directly rival iMac

1995-2015: How technology has changed the world in 20 years

Best Photoshop video tutorials: 8 video tutorial websites for Photoshop

Apple's event invitations decoded: A look back at 16 of Apple's most cryptic invites | Clues in…