Remove text from cells? 2

[ralphralph07]

Hello

I have a spreadsheet that in column A stored the following sample data:

1 min 8 sec
2 min 20 sec
33 sec
40 sec
2 min 42 sec
3 min 12 sec
53 sec
1 min 17 sec

I need to be able to sum these values as time, i have tried stripping out the text but when i change the format to time it all converts to 0:00

Any help gratefully received.

Ralph

 

[SkipVought]

Each value must be converted to DAYS (Date/Time) Then you can do arithmetic. Format to DISPLAY hh:mm.

faq68-5827

How many rows do you have?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ralphralph07]

Hi Skip

I have about 187 rows of data each day that come through like this?

Thanks

Ralph

 

[Gavona]

Use Data, text to columns to separate the different elements.
Then a formula like this to combine into the correct format (decimal days).
=(B3+D3/60)/60/24
B contains the minutes, D the seconds

Gavin

 

[ralphralph07]

Hi

Excellent - that works a treat!

Thank you

Ralph

 

[macropod]

hi Ralph,

Here's a formula you can use without Text to Columns:

=VALUE("0:"&IF(NOT(ISERROR(FIND(" sec",A1))),SUBSTITUTE(IF(NOT(ISERROR(FIND(" min",A1))),SUBSTITUTE(A1," min",":"),"0:"&A1)," sec",""),IF(NOT(ISERROR(FIND(" min",A1))),SUBSTITUTE(A1," min",":"),"0:"&A1)&"00"))

Simply format the result as time.

Cheers

[MS MVP - Word]