Excel成神之道-004-文本型时长转换成分钟数

接到老同学的一个case，真是把我难住了，想了各种招，做了各种优化，终于问题搞定，写这个文章以做记录和分享，需求问题如下所示（把左边文本时长变成右边的分钟数）： 

时长	分钟数
1天2小时5分	1565
2小时55分	175
3分	3
1天49分	1489
6小时	360
4天	5760

第一招：用辅助列和替换函数3次嵌套，3个步骤实现

1）添加一列，函数输入

="=="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"天","*24*60+"),"小时","*60+"),"分","")

获取到数学公式，形如：==1*24*60+2*60+5

​​​​​​​

2）复制该列，粘贴到另一列、或者原地粘贴，粘贴为数值

3）替换“==”为“=”，完成

最后结果如下图所示：

 第二招：用单公式实现，过程极为烧脑，不推荐，迫不得已情况下使用：

        思考步骤：

        1）先计算“天”，找到“天”，提取对应的数字*24*60得到天数对应的分钟数，如果找不到“天”，那么天数对应分钟数为0。

        2）计算“小时”，如果找到“天”，那么获取“天”到“小时”之间的数字，即为小时数，*60等于分钟数，如果没有获取到“天”，那么直接左截取函数获取到小时数并*60，如果出错就是没有小时，对应小时分钟数就是0。

        3）计算“分”，如果没找到“分”，直接等于0，否则就是有“分”，判断有没有”小时“，有的话获取”小时“和”分“之间的数字，即为分钟数，否则判断有没有”天“，有的话就是获取”天“和”分“之间的数字，即为分钟数，最后就是”天“和”小时“都没有找到的情况下，条件直接True()，直接获取“分”前面的数字为分钟数。

        4）三者相加，即为完整的时长分钟数。

        完整的函数如下：

=IFERROR(LEFT(A2,FIND("天",A2)-1)*24*60,0)+IFERROR(IF(IFERROR(FIND("天",A2),0),MID(A2,FIND("天",A2)+1,FIND("小时",A2)-FIND("天",A2)-1)*60,IFERROR(LEFT(A2,FIND("小时",A2)-1)*60,0)),0)+IFERROR(IFS(IFERROR(FIND("分",A2),0)=0,0,IFERROR(FIND("小时",A2),0),MID(A2,FIND("小时",A2)+2,FIND("分",A2)-FIND("小时",A2)-2),IFERROR(FIND("天",A2),0),MID(A2,FIND("天",A2)+1,FIND("分",A2)-FIND("天",A2)-1),TRUE(),LEFT(A2,FIND("分",A2)-1)),0)

这一个方法是最后一刻想出来的。。比较完美，各种情况都考虑了

之前有几个方法考虑的不是那么周全，没办法，我就喜欢追求完美，为了记录过程，我把考虑不周的函数也粘贴在这里。

1.如下该函数仅考虑到天、小时、分按顺序排列的情况，没有考虑到唯独没有小时，或者只有天或者只有小时的情况

=IFS(IFERROR(FIND("天",A2),0),LEFT(A2,FIND("天",A2)-1)*24*60+MID(A2,FIND("天",A2)+1,FIND("小时",A2)-FIND("天",A2)-1)*60+MID(A2,FIND("小时",A2)+2,FIND("分",A2)-FIND("小时",A2)-2),IFERROR(FIND("小时",A2),0),LEFT(A2,FIND("小时",A2)-1)*60+MID(A2,FIND("小时",A2)+2,FIND("分",A2)-FIND("小时",A2)-2),IFERROR(FIND("分",A2),0),LEFT(A2,FIND("分",A2)-1))

2.只比1多考虑了天和分都有，唯独没有小时的情况

=IFS(IFERROR(FIND("天",A2),0),IF(IFERROR(FIND("小时",A2),0),LEFT(A2,FIND("天",A2)-1)*24*60+MID(A2,FIND("天",A2)+1,FIND("小时",A2)-FIND("天",A2)-1)*60+MID(A2,FIND("小时",A2)+2,FIND("分",A2)-FIND("小时",A2)-2),LEFT(A2,FIND("天",A2)-1)*24*60+MID(A2,FIND("天",A2)+1,FIND("分",A2)-FIND("天",A2)-1)),IFERROR(FIND("小时",A2),0),LEFT(A2,FIND("小时",A2)-1)*60+MID(A2,FIND("小时",A2)+2,FIND("分",A2)-FIND("小时",A2)-2),IFERROR(FIND("分",A2),0),LEFT(A2,FIND("分",A2)-1))

最后总结：

1.没必要的情况下，用第一招吧。不那么烧脑，不容易出错，逻辑清晰

2.写的再好也有可能有纰漏，所以如果工作环境中，最好用另一个方法验证正确性，或者至少抽查验证

3.写函数一定小心引号，一定要英文逗号，我调试里的好多时间是浪费在引号上的。

4.我还是太菜了，暂时没有想到什么更好的办法，只能用这两种土办法，如果你们有更好的办法，请私信我。

PS：研究过程中发现我的Excel2016和WPS都不支持Lambda函数，悲从心生。。是不是要excel最新版本或Excel365才支持？