Text Time will not Convert to a Number

[jscranton]

I have a column of Time values that are currently formatted as text. I need to convert these to a time value. I have tried every trick I know of, including:

=(A1)*1 'where A1 is the Text Time value

=Entering 1 in a blank cell (B1 for example), then using Paste Special-->Edit-->multiply


I think the answer is using:

=TIMEVALUE(TEXT(

But I am stuck on the format for the text. My Text Time String looks like this:

18:04:56:0
20:46:14:0
13:22.26:0


ANy help is appreciated.

 

[lozzablake]

try

=TIMEVALUE(LEFT(A1,8))

where your text is in cell A1

 

[Andrew Poulsom]

Or:

=LEFT(A1,8)+0

 

[jscranton]

Thanks. That worked.

I think it worked because the last two characters 0) are the problem.

Thanks.