问题是,我有一个手机,说,
WH,QC-NDE(0.75),CHL150-1(5.05),HMCT12P1(1),BS(0.2),QC-NDE(0.5),TIGPEC05(17.25),SR,CHL150-1(23),HMCT12P1(42),BS(1.5),QC,QC-H(0.35),QC-NDE(0.75),INSP_FIN(1)
我想从左到右添加括号中的所有数字,直到到达第一个 CHL150-1。
如果CHL150-1前面有“SR”字样,则加72。
因此对于上述示例,返回的值将是 0.75 + 5.05 = 5.80,如粗体所示。
TIGPEC05(17.25),简写,CHL150-1(23),HMCT12P1(42),BS(1.5),QC,QC-H(0.35),QC-NDE(0.75),INSP_FIN(1)
上面返回的值将是 17.25 + 72 + 23 = 112.25。
理想情况下,可以修改触发结束的条件选择。(例如,将 CHL150-1 更改为 HMCT12P1)
非常感谢任何帮助。最好使用公式和 UDF 或两者的混合。谢谢!
答案1
Public Function mysum(source As String, delim As String) As Double
source = "," & source & ","
delim = "," & delim & "("
If InStr(source, delim) > 0 Then
source = Left(source, InStr(1 + InStr(source, delim), source, ","))
End If
If InStr(source, ",SR,") > 0 Then
mysum = 72
End If
Do Until InStr(source, "(") = 0
source = Mid(source, 1 + InStr(source, "("))
mysum = mysum + Val(source)
source = Mid(source, InStr(source, ")"))
Loop
End Function
在哪里
- 来源- 数据单元(包含“WH,QC-NDE(0.75), ... ,INSP_FIN(1)”)
- 德利姆- 分隔符终止符(“CHL150-1”、“HMCT12P1”...)
两个参数都可以是单元格地址或文字。
“SR”和 72 也可以从硬编码常量转换为(可选)参数。
如果您想重命名该函数,您必须替换代码中出现的所有函数(4次)。
答案2
此方法使用Split
函数
Option Explicit
Function SumSpecial(str As String) As Double
Dim V, W
Dim I As Long
Dim D As Double
Const strEnd As String = "CHL150-1"
Const str72 As String = "SR"
V = Split(str, ",")
I = 0
Do
W = Split(V(I), "(")
If UBound(W) = 1 Then
D = D + Val(W(1))
End If
If W(0) = str72 And _
Split(V(I + 1), "(")(0) = strEnd Then
D = D + 72
End If
I = I + 1
Loop Until W(0) = strEnd
SumSpecial = D
End Function