Text to Minutes:Seconds

  • Thread starter Thread starter beeawwb
  • Start date Start date
B

beeawwb

Good morning all.

I have recently been given an export of data from a program that i
text (delimited), which I can import fine. The problem I have is when
want to work with the data as minutes. By default, Excel is treatin
them as hours (because they are in the **:** format, not the **:**:*
format).

My question is this. Is there a way to take all my text value and mak
them all be 00:+My minutes:My Seconds? Nothing will go over 60 minutes
the largest value is 21:39, the smallest value is :31 (exactly lik
that. The program didn't export a leading zero for this. There are onl
3 values like this though, so I can change them myself if need be).

VBA Code would be fine, or any other way. Right now, cells ar
formatted as text (on import, because otherwise Excel imported them a
4:31:00, instead of 4:31.

Any help much appreciated.

-Bo
 
Hi and good evening :-)
one way: use a adjacent helper column:
- your data import is in column A, formated as text
- In B1 enter the following formula
=TIME(0,LEFT(A1,FIND(":",A1)-1),MID(A1,FIND(":",A1)+1,2))
and copy down (you may to format this row a time

- Select this column B, copy this column (CTRL+C)
- goto 'Edit - Paste Special' and insert the data as 'Values'
 
Well, that appears to be one way to do it. :)

I've used that for now, but it *did* have problems when it came acros
the values that were :31, :50, :39, as I predicted. But then, I don'
know if there are any solutions that could account for that.

Thanks once again.

-Bo
 
Hi
try
=TIME(0,IF(FIND(":",A1)>1,LEFT(A1,FIND(":",A1)-1),0),MID(A1,FIND(":",A1
)+1,2))
 
Back
Top