因此,我有一列名为化学式的列,可容纳40,000个条目,而我想做的就是计算化学式中包含的元素数量。例如:
EXACT_MASS FORMULA
626.491026 C40H66O5
275.173274 C13H25NO5
为此,我需要某种公式,其结果将返回
C H O
40 66 5
13 25 5
全部作为不同元素的单独列,并作为不同条目的行。有公式可以做到这一点吗?
您可以制定自己的公式。
使用ALT和打开VBA编辑器,F11然后插入一个新模块。
Microsoft VBScript Regular Expressions 5.5
通过单击工具,然后单击引用来添加引用。
现在添加以下代码:
Public Function FormulaSplit(theFormula As String, theLetter As String) As String
Dim RE As Object
Set RE = CreateObject("VBScript.RegExp")
With RE
.Global = True
.MultiLine = False
.IgnoreCase = False
.Pattern = "[A-Z]{1}[a-z]?"
End With
Dim Matches As Object
Set Matches = RE.Execute(theFormula)
Dim TheCollection As Collection
Set TheCollection = New Collection
Dim i As Integer
Dim Match As Object
For i = (Matches.Count - 1) To 0 Step -1
Set Match = Matches.Item(i)
TheCollection.Add Mid(theFormula, Match.FirstIndex + (Len(Match.Value) + 1)), UCase(Trim(Match.Value))
theFormula = Left(theFormula, Match.FirstIndex)
Next
FormulaSplit = "Not found"
On Error Resume Next
FormulaSplit = TheCollection.Item(UCase(Trim(theLetter)))
On Error GoTo 0
If FormulaSplit = "" Then
FormulaSplit = "1"
End If
Set RE = Nothing
Set Matches = Nothing
Set Match = Nothing
Set TheCollection = Nothing
End Function
用法:
FormulaSplit("C40H66O5", "H")
将返回66。FormulaSplit("C40H66O5", "O")
将返回5。FormulaSplit("C40H66O5", "blah")
将返回“未找到”。您可以直接在工作簿中使用此公式。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句