Remove the last 3 characters from a number

[toplad]

Hi guys,

Ive spent the last 2 hours trying to solve this problem and I've hit a wall.

I have this number in a cell 00:20:00 i'd like to remove the last three characters :00 iv'e tried using left and right but it doesn't work and outputs some random number and not 00:20 after striping the last 3 characters.

Basically what im trying to solve (just in case there's an easier way) is converting a list like the one below where it sometimes has the odd one with extra 00's in to minutes in the next column so 00:20 would be 20 and so on.

[TABLE="width: 61"]
<tbody>[TR]
[TD]00:20[/TD]
[/TR]
[TR]
[TD]00:20[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]00:20:00[/TD]
[/TR]
[TR]
[TD]00:30[/TD]
[/TR]
[TR]
[TD]00:20[/TD]
[/TR]
[TR]
[TD]00:30
01:05[/TD]
[/TR]
[TR]
[TD]00:30[/TD]
[/TR]
[TR]
[TD]00:20[/TD]
[/TR]
[TR]
[TD]00:20[/TD]
[/TR]
[TR]
[TD]00:20[/TD]
[/TR]
[TR]
[TD]00:20[/TD]
[/TR]
[TR]
[TD]00:30[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]00:30[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]00:20[/TD]
[/TR]
[TR]
[TD]00:20[/TD]
[/TR]
</tbody>[/TABLE]

 

[Cross Terrier]

=MID(A1,(FIND(":",A1)+1),2)

Assuming that all of the values you are looking for are two digits....

 

[toplad]

Yeah they should always be in the format of 00:00 they will never go into 00:00:00 its just for some reason a 00:00:00 format sneaks into the list somehow. I tried your code and it said value?

 

[TonyUK72]

Copy and paste down as required, make sure to remove the first & last speech marks.

"=text(a1,"hh:mm")"

 

[toplad]

Tony i owe you a pint thank you!!