仅从单元格值中获取数字 [英] Get only numbers from cell value

问题描述

我使用公式将3 hours 7 mins显示为3,07.但是，由于某些原因，同一公式在Win7 Office 2013英文版上不起作用，它显示为#VALUE.为什么会这样以及如何修复呢?也许其他公式都适合两者?

I use formula to display 3 hours 7 mins as 3,07. However for some reason the same formula does not work on Win7 Office 2013 English version it says #VALUE. Why so and how to repair it? Maybe other formula to suite for both?

Windows 10 Office 365欧洲版

Windows 10 Office 365 European version

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

Windows 7 Office 2013英文版

Windows 7 Office 2013 English version

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

推荐答案

3,7不是数字，因此当公式到达此点时:

3,7 is not a number, thus when the formula reaches this point:

=CEILING("3,7",0.1)

您将收到#VALUE错误.您可以改用以下命令使其正常工作(注意更改):

You will get the #VALUE error. You can use the following instead to make it work (notice the change):

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

这将导致3.7.如果需要使用逗号，则需要最后一个SUBSTITUTE:

Which results in 3.7. If you need it to be a comma, you will need a final SUBSTITUTE:

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

这篇关于仅从单元格值中获取数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！