练习题046答案：如何将文本“2小时47分49秒”转换为多少秒？

“2小时47分49秒”是字符串，要将其转换为多少秒，最常见的思路时用字符串函数分别提取出2、47，然后分别乘以3600、60，然后再加上字符串中的秒数，这是很常规的一种解法，那有没有另外的方法呢？

在介绍其他的解法之前，我们先来看一下使用Excel时遇到的一种情况：

我们在单元格中输入文本字符串“2017年3月3日”，Excel会自作聪明的变成“2017-3-3”，并显示为““2017年3月3日”，如果输入2017年3月“，它会自作聪明的变为“2017-3-1”。

同理，如果，我们输入”5时37分7秒“字符串，它会自动变为”5:37:07"，并显示为”5时37分7秒“，如果输入”7时28分“，它会自动变为”7:28:00"，并显示为”7时28分“。

看到这里，我们应该明白了，如果我们输入日期或时间字符串，它就自动将其转换为标准的日期或时间，并按输入的格式显示。也就是说，只要是标准格式的日期或时间字符串，Excel是可以将其转为标准的日期或时间的，那在公式中，能不能转换呢？

我们在A2单元格输入2时47分49秒，回车后，其自动变为“2:47:49”，用ISTEXT函数测试，计算结果为FALSE，说明它不是文本。

我们在A3输入公式="2时47分49秒"，回车后，它靠左显示，文本一般是靠左显示的，再用ISTEXT函数测试，计算结果为TRUE，说明它是文本。

然后，我们将A3的公式修改为=--"2时47分49秒"，也就是在文本字符串前加二个负号，这个用法，我们一般是用于将文本数字转换为真正的数值。回车后，A3的计算结果显示为0.116539352。我们知道，在Excel中日期和时间本质上就是数字，系统最早的日期是1900年1月1日，它是1，今天是2017年3月3日，实际上就是42796，也就是说从1900年1月1日到今天共42796天。一天24小时，一天是1，那么凌晨1点就是0.041667（1/24），中午是12时，刚好半天，也就是0.5。

我们将A3单元格设为A2单元格一样的时间格式，就可看到，它显示的和A2单元格一模一样。

因而我们可以得出结论，在公式中，字符串的日期和时间，也是可以转换为标准的日期和时间的。

所以，“2小时47分49秒”这种非标准的时间，我们只要将“小”字删除（可用SUBSTITUTE删除“小”），然后在前面加两个负号（负负得正）就可转换为标准时间，然后再乘以86400（24*60*60）即可。

转换的公式很简单：

=--SUBSTITUTE(A2,"小",)*86400

前面的那种情况用上面的公式没问题，但如果下面这种情况，还是套用此公式就会出错

由于原数据有些时间并没有小时，甚至没有分，那么对文本做算术运算时（添加负号）就会出错，为避免这种情况，我们先删除“小”，然后在原数据前依次加上"0时0分"、"0时"、0，添加后补全，将其转换为负数，这时那些添加字符后如果是非标准时间，转换为负数时就会出错，然后用LOOKUP查找0，利用LOOKUP会忽略错误值特点，将错误值忽略掉，再次乘以负数和86400，就会得出正确结果。完整的公式为：

=-LOOKUP(,-({"0时0分","0时",0}&SUBSTITUTE(A2,"小",)))*86400

上面公式编制思路的完整解释参见下图

C2、C5、C8是给时间字符串前添加"0时0分"，公式为：="0时0分"&B2

C3、C6、C9是给时间字符串前添加"0时"，公式为：="0时"&B3

C4、C7、C10是给时间字符串前添加0，公式为：=0&B4