Try this:

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))


"Micki" wrote:

I have raw data that downloads in 2 different formats.
451 22:07:34 (451 = # days a problem is not resolved)
3:39:10 (hrs, minutes, seconds)
I need a formula that will enter only the hrs, mins and seconds in an
adjacent column. The formula =RIGHT(A33,SEARCH(":",A33)+2) gives me the
results I need where the data in column A = 451 22:07:34, but I believe I
also need to add an if statement to give me only the hrs, mins, sec for the
case where the value is 3:39:10. I have tried using
=if(len(a27)8,RIGHT(A33,SEARCH(":",A33)+2),A27, but I just get #Value
return. Help is greatly appreciated.