只从单元格值中获取数字

Question

我使用公式将3 hours 7 mins显示为3,07。然而，出于某种原因，同样的公式在Win7 Office 2013英文版上不起作用，它说#VALUE。为什么是这样，怎么修理呢？也许两个人都有其他的套餐吗？

Windows 10 Office 365欧洲版

=CEILING(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("hour"; R96)); "0 hours "; "")&R96;"s";"");" min";"");" hour ";",");0,1)

Windows 7 Office 2013版

=CEILING(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("hour", P96)), "0 hours ", "")&P96,"s","")," min","")," hour ",","),0.1)

Answer 1

3,7不是一个数字，因此，当公式达到这个点时：

=CEILING("3,7",0.1)

您将得到#VALUE错误。您可以使用以下方法使其工作(请注意更改)：

=CEILING(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("hour", P96)), "0 hours ", "")&P96,"s","")," min","")," hour ","."),0.1)
                                                                                                                            ^

这导致了3.7。如果您需要它是逗号，则需要一个最终的SUBSTITUTE。

=SUBSTITUTE(CEILING(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("hour", P96)), "0 hours ", "")&P96,"s","")," min","")," hour ","."),0.1), ".", ",")

Answer 2

这看起来像是不同国家之间的十进制分隔符造成的一个问题:一些人把“1,001一半”写成"1,000.5"，另一些人写成"1.000,5“

因此，您需要确保您的","知道什么时候应该是"."。我能想到的最简单的方法就是MID(1/2,2,1) --也就是，从一半中取中间字符。根据您的设置，这将是0.5 (结果：".")或0,5 (结果：",")。

(最后的0.1应该在不同版本之间自动更改，因为它是一个实际的数字--如果不是，那么就将它更改为1/10以获得类似的结果)

=CEILING(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("hour"; R96)); "0 hours "; "")&R96;"s";"");" min";"");" hour ";MID(1/2,2,1));1/10)

还需要考虑:您当前的系统将将"2小时1分钟“写为"2,1”(或"2.1“，视设置而定)--但它也会将"2小时10分钟”写成"2,1“。您的需求可能需要重新审核。