1、图1时长在A1:A100。
B1输入=IF(ISNUMBER(FIND("天",A1)),LEFT(A1,(FIND("天",A1)-1)),"") 下拉填充至B100,
C1输入=IF(B1="",IF(ISNUMBER(FIND("小时",A1)),LEFT(A1,(FIND("小时",A1)-1)),""),MID(A1,FIND("天",A1)+1,FIND("小时",A1)-FIND("天",A1)-1))下拉填充至C100,
D1输入=IF(C1="",IF(ISNUMBER(FIND("分钟",A1)),LEFT(A1,(FIND("分钟",A1)-1)),""),MID(A1,FIND("小时",A1)+2,FIND("分钟",A1)-FIND("小时",A1)-2))下拉填充至D100
E1输入=IF(D1="",IF(ISNUMBER(FIND("秒",A1)),LEFT(A1,(FIND("秒",A1)-1)),""),MID(A1,FIND("分钟",A1)+2,FIND("秒",A1)-FIND("分钟",A1)-2))下拉填充至E100,
这样B,C,D,E列分别为提取出的天,小时,分钟,秒的数值, 然后就可以根据你的需要 统一换算成小时或者分钟求和啦!
2、图2时长列在G2:Gn,且分钟为分时。
K2=IF(ISNUMBER(FIND("天",G2)),LEFT(G2,(FIND("天",G2)-1)),"")
L2=IF(K2="",IF(ISNUMBER(FIND("小时",G2)),LEFT(G2,(FIND("小时",G2)-1)),""),MID(G2,FIND("天",G2)+1,FIND("小时",G2)-FIND("天",G2)-1))
M2=IF(L2="",IF(ISNUMBER(FIND("分",G2)),LEFT(G2,(FIND("分",G2)-1)),""),MID(G2,FIND("小时",G2)+2,FIND("分",G2)-FIND("小时",G2)-2))
N2=IF(M2="",IF(ISNUMBER(FIND("秒",G2)),LEFT(G2,(FIND("秒",G2)-1)),""),MID(G2,FIND("分",G2)+1,FIND("秒",G2)-FIND("分",G2)-1))
标签:G2,excel,累加,A1,XX,ISNUMBER,FIND,LEFT From: https://www.cnblogs.com/vivih-y/p/18337973