在论坛找的题目

云影

附件内有模拟答案
前10楼公式最短者最佳

蝶·舞

瞎鼓捣了一个，算是抛砖引玉

1. =TEXT(-LOOKUP(,-({0,"0时","0时0分"}&SUBSTITUTE(TEXT(A2,"h:m:s"),"时","时0分"))),LEFT("H时M分S秒",2+COUNTIF(A2,"*秒")*4+COUNTIF(A2,"*分*")*2))

复制代码

lyf7276

天，怎么这么长？等下再来个短的。
IF(LEN(CHOOSE(ISERROR(FIND("时",A2))+1,A2,"0时"&A2))=FIND("时",CHOOSE(ISERROR(FIND("时",A2))+1,A2,"0时"&A2)),CHOOSE(ISERROR(FIND("时",A2))+1,A2,"0时"&A2),CHOOSE((ISERROR(FIND("分",CHOOSE(ISERROR(FIND("时",A2))+1,A2,"0时"&A2)))+1),CHOOSE(ISERROR(FIND("时",A2))+1,A2,"0时"&A2),LEFT(CHOOSE(ISERROR(FIND("时",A2))+1,A2,"0时"&A2),FIND("时",CHOOSE(ISERROR(FIND("时",A2))+1,A2,"0时"&A2)))&"0分"&RIGHT(CHOOSE(ISERROR(FIND("时",A2))+1,A2,"0时"&A2),LEN(CHOOSE(ISERROR(FIND("时",A2))+1,A2,"0时"&A2))-FIND("时",CHOOSE(ISERROR(FIND("时",A2))+1,A2,"0时"&A2)))))

rlz680926

=IF(RIGHT(A2,1)="时",A2,SUBSTITUTE(IF(IFERROR(FIND("分",A2),0)=0,REPLACE(IF(IFERROR(FIND("时",A2),0)=0,"0时"&A2,A2),FIND("时",IF(IFERROR(FIND("时",A2),0)=0,"0时"&A2,A2))+1,,"0分"),IF(IFERROR(FIND("时",A2),0)=0,"0时"&A2,A2)),"分0","分"))

lyf7276

这个感觉短了不少
IF(IFERROR(FINDB("秒",IF(SUBSTITUTE(A2,"时","")=A2,"0时"&A2,A2)),8)<8,SUBSTITUTE(IF(SUBSTITUTE(A2,"时","")=A2,"0时"&A2,A2),LEFT(IF(SUBSTITUTE(A2,"时","")=A2,"0时"&A2,A2),FIND("时",IF(SUBSTITUTE(A2,"时","")=A2,"0时"&A2,A2))),LEFT(IF(SUBSTITUTE(A2,"时","")=A2,"0时"&A2,A2),FIND("时",IF(SUBSTITUTE(A2,"时","")=A2,"0时"&A2,A2)))&"0分"),IF(SUBSTITUTE(A2,"时","")=A2,"0时"&A2,A2))

opqazxc

堆下公式
03版本过嵌套层数了
所以改10版本了

1. =SUBSTITUTE(SUBSTITUTE(TEXT(SUM(--("0 "&TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"时","/24"&REPT(" ",50)),"分","/1440"&REPT(" ",50)),"秒","/864%"),{1,50,100},50)))),"h时m分s秒"),"时0分0秒","时"),"分0秒","分")

复制代码

睡觉去了
再减点

1. =SUBSTITUTE(SUBSTITUTE(TEXT(SUBSTITUTE(IF(COUNTIF(A3,"*时*"),,"0时")&A3,"时",IF(COUNTIF(A3,"*分*"),"时","时0分")),"h时m分s秒"),"0分0秒",),"0秒",)

复制代码

再减   一天又过去了

1. =TEXT(SUBSTITUTE(IF(COUNTIF(A2,"*时*"),,"0时")&A2,"时",IF(COUNTIF(A2,"*分*"),"时","时0分")),LEFT("h时m分s秒",FIND(RIGHT(A2),"时分秒")*2))

复制代码

又要去休息咯

笨笨四

SUBSTITUTE(SUBSTITUTE(CHOOSE(ABS(MOD(SUM(ISERR(FIND({"时","分","秒"},A2))*{1,2,4}),4)-2)+1,TEXT(TIMEVALUE(REPLACE(A2,3,,"0分")),"h时m分s秒"),TEXT(TIMEVALUE(IF(ISERR(FIND("分",A2)),"0时0分"&A2,"0时"&A2)),"h时m分s秒"),TEXT(A2,"[h]时m分s秒")),"0分0秒",),"0秒",)

支持云兄。。发个臭长的。