A text 1.10.5 means 1 minute & 10.5 second has been input into a cell, how
can I have it changed to 70.5 second. Could somebody help.

On Tue, 31 Jan 2006 20:22:28 -0800, "AlanW"
wrote:

A text 1.10.5 means 1 minute & 10.5 second has been input into a cell, how
can I have it changed to 70.5 second. Could somebody help.

Try:

=LEFT(A1,FIND(".",A1)-1)*60+MID(A1,FIND(".",A1)+1,10)

in an adjacent column will change the value to seconds.

If you actually need to have it changed IN THE CELL, then we will need to write
a VBA event-triggered macro.

This formula should work for data in the format you specified.

That means that 10.5 seconds needs to be entered as:

0.10.5

If there are other possible variations, you'll need to post them.




--ron

"Ron Rosenfeld" wrote:

On Tue, 31 Jan 2006 20:22:28 -0800, "AlanW"
wrote:

A text 1.10.5 means 1 minute & 10.5 second has been input into a cell, how
can I have it changed to 70.5 second. Could somebody help.

Try:

=LEFT(A1,FIND(".",A1)-1)*60+MID(A1,FIND(".",A1)+1,10)

in an adjacent column will change the value to seconds.

If you actually need to have it changed IN THE CELL, then we will need to write
a VBA event-triggered macro.

This formula should work for data in the format you specified.

That means that 10.5 seconds needs to be entered as:

0.10.5

If there are other possible variations, you'll need to post them.




--ron
Thank you very much, I have done it.

On Tue, 31 Jan 2006 22:59:27 -0800, "AlanW"
wrote:


Thank you very much, I have done it.

You're welcome. Thanks for the feedback.
--ron