Data conversion

A

AlanW

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.
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
A

AlanW

Ron Rosenfeld said:
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.
Click to expand...
 
Top