Converting imported text into number format

[Guest]

I currently import data from a Telephony Caller Management System called CentreVu. It provides details for example on the length of time an employee has been available to answer Telephone Calls ar how long they have spent on lunch/breaks etc

When I export data, total times exported over one hour export in the following format - "01:59:03", and are formatted as a Time. Which is what I need.
Any total times exported as less than one hour, export as ":59:03", which is formatted as a text, and as such can not be manipulated with formulas etc

Is there any way of reformatting this text (":59:03") as a number. At present I am having to labouriously insert a "0" before every total that exports as text (":59:03")

I have tried the concanetate function to insert a "0", and this will insert a "0" as required i.e. "0:59:03" but the cell is still formatted as text and I cannot use function such as "Sum" and "SumIF"

Help

 

[Andy B]

Hi

To convert these to numbers try the following:
Copy a blank unused cell.
Select the range of 'numbers'
Go to Edit / Paste Special and select Add and then click OK

Hope this helps

--
Andy.

I currently import data from a Telephony Caller Management System called

CentreVu. It provides details for example on the length of time an employee
has been available to answer Telephone Calls ar how long they have spent on
lunch/breaks etc.

When I export data, total times exported over one hour export in the

following format - "01:59:03", and are formatted as a Time. Which is what I
need.

Any total times exported as less than one hour, export as ":59:03", which

is formatted as a text, and as such can not be manipulated with formulas
etc.

Is there any way of reformatting this text (":59:03") as a number. At

present I am having to labouriously insert a "0" before every total that
exports as text (":59:03").

I have tried the concanetate function to insert a "0", and this will

insert a "0" as required i.e. "0:59:03" but the cell is still formatted as
text and I cannot use function such as "Sum" and "SumIF".

 

[Peo Sjoblom]

You can use a help column and this formula

=IF(LEFT(A1)=":",--("00"&A1),A1)

copy down and format as time, it will probably return
the decimal value but I tested and once you format as time
it will be 00:59:03

--

Regards,

Peo Sjoblom

I currently import data from a Telephony Caller Management System called

CentreVu. It provides details for example on the length of time an employee
has been available to answer Telephone Calls ar how long they have spent on
lunch/breaks etc.

When I export data, total times exported over one hour export in the

following format - "01:59:03", and are formatted as a Time. Which is what I
need.

Any total times exported as less than one hour, export as ":59:03", which

is formatted as a text, and as such can not be manipulated with formulas
etc.

Is there any way of reformatting this text (":59:03") as a number. At

present I am having to labouriously insert a "0" before every total that
exports as text (":59:03").

I have tried the concanetate function to insert a "0", and this will

insert a "0" as required i.e. "0:59:03" but the cell is still formatted as
text and I cannot use function such as "Sum" and "SumIF".