vba - 使用公式从字符串中提取两个数字

问题描述

我有从 Google XML 响应中提取数字的公式，可以是：

9 часов 24 минут
10 h 0 min
9 h 20 min
11 h 13 min
10 Std 55 min
9 timmar 5 min
19 min

这是当前公式（值在 BZ1 中）：

=IFERROR(IF(VALUE(IF(FIND("min";BZ1;1)=11;MID(BZ1;FIND("min";BZ1;1)-2;1);MID(BZ1;FIND("min";BZ1;1)-3;2)))<30; VALUE(LEFT(BZ1;FIND("h";BZ1;1)-2))&","&5;VALUE(LEFT(BZ1;FIND("h";BZ1;1)-2))+1);"")

例如，公式将小时和分钟舍入到小时

1 h 39 min->2
10 h 12 min->10,5
9 h 20 min->9,5

存在无法考虑和的语言变化的hours问题min。

有没有可能让它工作，以便它：

如果只有数字（案例19 min）-> 提取数字
如果有两个数字（大小写1 h 39 min）-> 将第一个数字提取为小时，然后从最后两个空格中提取数字为分钟

编辑：

检查单元格中有多少个数字（CA25 中的目标）：

SUM(LEN(CA25)-LEN(SUBSTITUTE(CA25;{1;2;3;4;5;6;7;8;9};)))>1

如果超过 1

LEFT(CA25;(FIND(" ";CA25;1)-1))&TRIM(MID(SUBSTITUTE(CA25;" ";REPT(" ";50));100;50))

如果小于 1

LEFT(CA25;SUM(LEN(CA25)-LEN(SUBSTITUTE(CA25;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};""))))

全部一起

=IF(SUM(LEN(CA25)-LEN(SUBSTITUTE(CA25;{1;2;3;4;5;6;7;8;9};)))>1;LEFT(CA25;(FIND(" ";CA25;1)-1))&TRIM(MID(SUBSTITUTE(CA25;" ";REPT(" ";50));100;50));LEFT(CA25;SUM(LEN(CA25)-LEN(SUBSTITUTE(CA25;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};"")))))

这给出了输出：

现在这些需要转换为小时并四舍五入为一小时

编辑2：

这是公式（目标 BZ1）：

=IFERROR(IF(LEN(BZ1)-LEN(SUBSTITUTE(BZ1;" ";""))>2;LEFT(BZ1;(FIND(" ";BZ1;1)-1))+IF(TRIM(MID(SUBSTITUTE(BZ1;" ";REPT(" ";50));100;50))<60;1;1);IF(LEFT(BZ1;SUM(LEN(BZ1)-LEN(SUBSTITUTE(BZ1;{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};""))))<60;1;1));"")

标签： vbaexcel-formula

解决方案

这是一个小的用户定义函数：

Option Explicit
Public Function Tyme(inpt As String) As Double
    Dim arr, U As Long
    Tyme = 0
    arr = Split(inpt, " ")
    U = UBound(arr)
    If U = 0 Then Exit Function
    If U = 1 Then
        Tyme = CDbl(arr(0))
    Else
        Tyme = CDbl(arr(0)) + CDbl(arr(2)) / 60#
    End If
End Function

它：

与语言无关
返回未舍入的浮点值（小时）

一些例子：

用户定义函数 (UDF) 非常易于安装和使用：

ALT-F11 调出 VBE 窗口
ALT-I ALT-M 打开一个新模块
粘贴这些东西并关闭 VBE 窗口

如果您保存工作簿，UDF 将与它一起保存。如果您使用的是 2003 年以后的 Excel 版本，则必须将文件另存为 .xlsm 而不是 .xlsx

要删除UDF：

如上所述调出 VBE 窗口
清除代码
关闭 VBE 窗口

要使用Excel 中的 UDF：

=Tyme(A1)

和

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

有关 UDF 的详细信息，请参阅：

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

必须启用宏才能使其正常工作！

笔记：

四舍五入应在 udf之外应用
修改 udf 以处理秒数也很容易
修改 udf 以返回真正的 Excel 时间而不是浮点时间很容易

vba - 使用公式从字符串中提取两个数字

问题描述

解决方案

推荐阅读