Try my correction (below), and if that doesn't work, post a sample of your data
to www.savefile.com and we'll see if we can figure out what's up.

=IF(COUNT(FIND(" ",A1)),LEFT(A1,FIND(" ",A1)-1)+
MID(A1,FIND(" ",A1)+1,LEN(A1)),--A1)


Micki wrote:
It's returning #VALUE

"Glenn" wrote:

=IF(COUNT(FIND(" ",A1)),LEFT(A1,FIND(" ",A1)-1)+ (RIGHT(A1,SEARCH(":",A1)+1)),--A1)


Micki wrote:
Sorry for not asking this sooner. The person i was trying to assist just came
back to me this morning with the all-in result she was looking for. Where the
data in the cell contains the # of days an issue is outstanding, she wants to
convert that to hours and add it to the hh:mm:ss. So where the data = 451
22:07:34 she wants to multiply 451 x 24 hrs, and add it to 22 hrs, 7 mins and
27 seconds. Please note that not all data contains # days outstanding. Some
only contain 3:39:10 (hh:mm:ss) I am really stumped.)

"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.