Extract timings from text string in Excel

I have been provided with a lot of timings from a Unix process, they appear in the format below;

system001: real 9m31.14s

system002: real 4m31.14s

system007: real 18m2.14s

system1开发者_JAVA百科04: real 40m31.80s

These appear as a single column of data, with each system being on a unique row in an Excel worksheet.

I would like to be able to extract the minute and second values into a time based field so that I can calculate average runtimes, total runtimes etc.

What's the best way of achieving this given that I have thousands of rows of data?

Thanks in advance!

---

Assuming your value is in column A, you could use these two formulae:

Minutes: =LEFT(RIGHT(A1,LEN(A1) -4- SEARCH("real ",A1)),SEARCH("m",RIGHT(A1,LEN(A1) -4- SEARCH("real ",A1)))-1)
Seconds: =MID(RIGHT(A1,LEN(A1) -4- SEARCH("real ",A1)),SEARCH("m",RIGHT(A1,LEN(A1) -4- SEARCH("real ",A1)))+1, LEN(RIGHT(A1,LEN(A1) -4- SEARCH("real ",A1))) -SEARCH("m", RIGHT(A1,LEN(A1) -4- SEARCH("real ",A1)))-1)

OR the way I did was to take the common formula out into column B to reduce repetition with formula: =RIGHT(A1,LEN(A1) -4- SEARCH("real ",A1)) and then simplify the other two to use this (and allow Excel to reuse the last calculation instead of doing it over and over):

Minutes: =LEFT(B1,SEARCH("m",B1)-1)
Seconds: =MID(B1,SEARCH("m",B1)+1, LEN(B1) -SEARCH("m", B1)-1)

Of course you can still optimise this even more to calculate the index of 'm' just once and reuse that, but I'm sure you get the idea.

---

text to columns

Alternatively you can use text to columns option to repeatedly split text in the column

• split based on colon
• split based on space
• split based on m
• split based on s

Here is macro to do it automatically !

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Space:=False, OtherChar:=":"
    Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, Space:=True
    Columns("B:B").Delete
    Columns("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, OtherChar:="m"
    Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, OtherChar:="s"
End Sub