Converting number to minutes and deleting text

comic

Board Regular
Joined
Dec 11, 2012
Messages
72
The incoming Data is always inserted into column D

EG 1m 12.66s
2m 23.44s
1m 35.32s

I want to be able to convert these times to :

72.66
143.44
95.32

with no text, no spaces but keeping the decimal point so that I can calculate. You will notice that there is a space on the original Data between the "m" and the next number.

Macro would be helpful

Thank you.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Not quite clear how many variants you have - hours, no minutes, non-time values interspersed in Col D etc... the below is a rough and ready approach

Code:
Sub Example()
    With Sheets("Sheet1")
        With .Range(.Cells(1, "D"), .Cells(.Rows.Count, "D").End(xlUp))
            .Value = .Parent.Evaluate("IF(RIGHT(" & .Address & ",1)=""s""," & _
                        """0:""&SUBSTITUTE(SUBSTITUTE(" & .Address & ",""m "","":""),""s"",""0""),REPT(" & .Address & ",1))")
        End With
    End With
End Sub

the above would return values in time format which you can subsequently format as [ss].000 to see the cumulative seconds... if you prefer to have the seconds as integer you can apply a multiple of 86400.

modify reference to Sheet1 as appropriate.
 
Last edited:
Upvote 0
A formula like =24*60*60*TIMEVALUE("0:"&SUBSTITUTE(SUBSTITUTE(D1,"m ",":"),"s","")) would work.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top