excel提取文本格式时分秒中数字的方法并计算成秒的公式

类似于这样的时间格式我们是没办法参数计算的,可用下面的公式计算

以下公式中只需要将E2修改为数据所在单元格即可出结果(计算结果为秒,其它时间格式请自行转换)

=NUMBERVALUE(NUMBERVALUE(IFERROR(MID(E2,1,FIND("小",E2)-1),"0"))*3600+(IFERROR(NUMBERVALUE(IFERROR(MID(E2,1,FIND("分",E2)-1),"0")),"0")+NUMBERVALUE(IFERROR(MID(E2,FIND("时",E2)+1,(FIND("分",E2)-FIND("时",E2)-1)),"0")))*60+(NUMBERVALUE(IFERROR(NUMBERVALUE(IFERROR(MID(E2,1,FIND("秒",E2)-1),"0")),"0"))+NUMBERVALUE(IFERROR(MID(E2,FIND("分",E2)+1,(FIND("秒",E2)-FIND("分",E2)-1)),"0"))+NUMBERVALUE(IFERROR(MID(E2,FIND("钟",E2)+1,(FIND("秒",E2)-FIND("钟",E2)-1)),"0")))

原理: (下文中有,原文中也很详细,有需要理解的请跳转原文查看)

小时值 提取小前面的字符=IFERROR(MID(E2,1,FIND("小",E2)-1),"0")分钟值 提取分前面的字符=IFERROR(MID(E2,1,FIND("分",E2)-1),"0")秒钟值 提取秒前面的字符=IFERROR(MID(E2,1,FIND("秒",E2)-1),"0")

分钟值2 提取分与时之间的分钟值=IFERROR(MID(E2,FIND("时",E2)+1,(FIND("分",E2)-FIND("时",E2)-1)),"0")秒值2 提取分和秒之间的秒数值=IFERROR(MID(E2,FIND("分",E2)+1,(FIND("秒",E2)-FIND("分",E2)-1)),"0")秒值3 提取小时和秒之间的秒数值=IFERROR(MID(E2,FIND("钟",E2)+1,(FIND("秒",E2)-FIND("钟",E2)-1)),"0")

将小时值返回的结果中提取数字(去掉空格与非数字格式)              参与最后计算=NUMBERVALUE(IFERROR(MID(E2,1,FIND("小",E2)-1),"0"))将分钟值返回的结果中提取数字(去掉空格与非数字格式)      注:(返回错误值,下面再次计算)=NUMBERVALUE(IFERROR(MID(E2,1,FIND("分",E2)-1),"0"))将秒钟值返回的结果中提取数字(去掉空格与非数字格式)       注:(返回错误值,下面再次计算)=NUMBERVALUE(IFERROR(MID(E2,1,FIND("秒",E2)-1),"0"))将分钟值2返回的结果中提取数字(去掉空格与非数字格式)             参与最后计算=NUMBERVALUE(IFERROR(MID(E2,FIND("时",E2)+1,(FIND("分",E2)-FIND("时",E2)-1)),"0"))将秒值2返回的结果中提取数字(去掉空格与非数字格式)                参与最后计算=NUMBERVALUE(IFERROR(MID(E2,FIND("分",E2)+1,(FIND("秒",E2)-FIND("分",E2)-1)),"0"))将秒值3返回的结果中提取数字(去掉空格与非数字格式)                 参与最后计算 =NUMBERVALUE(IFERROR(MID(E2,FIND("钟",E2)+1,(FIND("秒",E2)-FIND("钟",E2)-1)),"0"))

将分钟值返回的错误值再次返回0                            注:(返回格式中带空格,还不能直接计算,下面再次修改为数字才能正式计算结果)                        =IFERROR(NUMBERVALUE(IFERROR(MID(E2,1,FIND("分",E2)-1),"0")),"0")将秒钟值返回的错误值再次返回0                            注:(返回格式中带空格,还不能直接计算,下面再次修改为数字才能正式计算结果)=IFERROR(NUMBERVALUE(IFERROR(MID(E2,1,FIND("秒",E2)-1),"0")),"0")

再次将分钟值返回的结果中提取数字(去掉空格与非数字格式)             参与最后计算  =NUMBERVALUE(IFERROR(NUMBERVALUE(IFERROR(MID(E2,1,FIND("分",E2)-1),"0")),"0"))再次将秒钟值返回的结果中提取数字(去掉空格与非数字格式)             参与最后计算=NUMBERVALUE(IFERROR(NUMBERVALUE(IFERROR(MID(E2,1,FIND("秒",E2)-1),"0")),"0"))

方法来源于:  CSDN博主「陵小宇」的原创文章

https://blog.csdn.net/weixin_39960920/article/details/110523783

https://blog.csdn.net/weixin_35318150/article/details/112707720

原理如下:

步骤一：提取小时之前的值，也就是提取文本“小”所在位置之前的值。

“小”所在位置之前的字符值，公式为=FIND("小",F3)-1；

从第一个字符起，提取“小”之前的文本串，公式为：

步骤二：提取“时”和“分”之间的分钟值。

首先返回“时”和“分”之间的字符长度，公式为：

接着继续用MID函数来提取文本，和小时值不同的是，这次开始的值是从“时”后面的字符开始的，也就是公式

提取分钟的值公式：

步骤三：提取“钟”和“秒”之间的秒数值，原理跟提取分钟值一样。

根据以上的公式，我们分别提取了小时、分钟、和秒的值，那接下来是不是可以用公式计算出秒数值呢？答案是不可以的，因为MID函数返回的是文本串，不能直接用于计算的，上图中显示的提取值靠左，而不是数值默认的靠右。

现在还出现了另外一个问题，对于“31分钟23秒”、“16秒”也就是没有包含的小时的值，我们无法提取分钟值，而可以提取秒的值，因为上面提取分钟值得公式是需要找到“时”的字符才有效，因此我们这里的返回值是错误的。

为了能够有效地提取这两种格式文本的值，我们还需要添加两个公式来提取。

分钟值：

秒钟值：

我们用MID和FIND函数已经将数值提取出来，但是一些错误的返回值显示“#VALUE!”，此时我们需要检验错误返回值，并将错误值返回指定值。

IFERROR(value, value_if_error)

H3=IFERROR(MID(F3,1,FIND("小",F3)-1),0)I3=IFERROR(MID(F3,FIND("时",F3)+1,(FIND("分",F3)-FIND("时",F3)-1)),0)如此类推算出J列跟K列，结果如下图：

我们可以看出H、I、J、K、L列部分返回的值靠左，意味着这些返回值只是“文本”，并不是数值。如果需要计算出具体的时间，还需要将文本转化成数值。

很多同学都用过VALUE函数，将文本值转化成数值，今天我要介绍的是万金油函数，NUMBERVALUE能够将空格等少数不规范文本强制转化为0，只返回文本中含有的单独数值，如果含有文本字母，将返回错误值，本次将用该函数以解决K列和L列的含有多有多余数值的文本。

NUMBERVALUE(Text, [Decimal_separator], [Group_separator ])

我们这里用NUMBERVALUE函数将H列到L列以后得到以下结果：

K3=NUMBERVALUE(IFERROR(MID(F3,1,FIND("分",F3)-1),0))L3=NUMBERVALUE(IFERROR(MID(F3,1,FIND("秒",F3)-1),0))再次用IFERROR函数以后即可将错误值返回为0.

K3=IFERROR(NUMBERVALUE(IFERROR(MID(F3,1,FIND("分",F3)-1),0)),0)L3=IFERROR(NUMBERVALUE(IFERROR(MID(F3,1,FIND("秒",F3)-1),0)),0)

到这一步，已经将所有时间的数值提取出来，即可计算出所有时间转化为秒的数值。

原公式

 NUMBERVALUE(IFERROR(MID(F3,1,FIND("小",F3)-1),0))*3600+NUMBERVALUE(IFERROR(MID(F3,FIND("时",F3)+1,(FIND("分",F3)-FIND("时",F3)-1)),0))*60+NUMBERVALUE(IFERROR(MID(F3,FIND("钟",F3)+1,(FIND("秒",F3)-FIND("钟",F3)-1)),0))+IFERROR((NUMBERVALUE(IFERROR(MID(F3,1,FIND("分",F3)-1),0))),0)*60+IFERROR((NUMBERVALUE(IFERROR(MID(F3,1,FIND("秒",F3)-1),0))),0) 

原公式中有问题,部分结果是不准确的————————————————版权声明：本文为CSDN博主「陵小宇」的原创文章原文链接：https://blog.csdn.net/weixin_35318150/article/details/112707720