Convert hhhh:mm:ss to hh:mm:ss (macro ?)

[Guest]

British Telecom use hhhh:mm:ss as a time format for length of call in
downloaded accounts. Excel97 does not recognise this as a number so I cannot
reformat to hh:mm:ss. I've tried (with no experience) a macro to remove the 2
leading zeros, but if I set it up by converting the first 10 times, then try
to run it starting at the 11th time, it does not convert the second set of
ten, but simply replicates the times created (for the first ten) when setting
up the macro.

BT Bill

 

[Ron Rosenfeld]

British Telecom use hhhh:mm:ss as a time format for length of call in
downloaded accounts. Excel97 does not recognise this as a number so I cannot
reformat to hh:mm:ss. I've tried (with no experience) a macro to remove the 2
leading zeros, but if I set it up by converting the first 10 times, then try
to run it starting at the 11th time, it does not convert the second set of
ten, but simply replicates the times created (for the first ten) when setting
up the macro.

BT Bill

No need for a macro. You can use the formula:

=--MID(A1,3,8)

to do the conversion.

You may also use the Data/Text to Columns wizard to do a conversion in place if
the range is in a column.

1. Select the range.
2. Date/Text to Columns
3. Fixed Width
Next
4. Drop a break line just after the 00
Next
5. The first column (00) will be selected. Check the "Do not import column"
box.
6. Finish

The above could be recorded as a macro.

Finally, to do this as a macro, after selecting the range of values to convert:

========================
Sub foo()
Dim c As Range

For Each c In Selection
c.Value = Mid(c.Text, 3, 8)
Next c

End Sub
==========================


--ron