Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.1k views
in Technique[技术] by (71.8m points)

vba - Extract numbers from chemical formula

Apologies if this has already been asked and answered but I couldn't find a satisfactory answer.

I have a list of chemical formulas including, in this order: C, H, N and O. And I would like to pull the number after each of these letters. The problem is that not all the formulas contain an N. All contain a C, H and O however. And the number can be either single, double or (in the case of H only) triple digit.

Thus the data looks like this:

  • C20H37N1O5
  • C10H12O3
  • C20H19N3O4
  • C23H40O3
  • C9H13N1O3
  • C14H26O4
  • C58H100N2O9

I'd like each element number for the list in separate columns. So in the first example it would be:

20 37 1 5

I've been trying:

=IFERROR(MID(LEFT(A2,FIND("H",A2)-1),FIND("C",A2)+1,LEN(A2)),"") 

to separate out the C#. However, after this I get stuck as the H# is flanked by either an O or N.

Is there an excel formula or VBA that can do this?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Use Regular Expressions

This is a good task for regular expressions (regex). Because VBA doesn't support regular expressions out of the box we need to reference a Windows library first.

  1. Add reference to regex under Tools then References enter image description here

  2. and selecting Microsoft VBScript Regular Expression 5.5 enter image description here

  3. Add this function to a module

     Option Explicit 
    
     Public Function ChemRegex(ByVal ChemFormula As String, ByVal Element As String) As Long
         Dim strPattern As String
         strPattern = "([CNHO])([0-9]*)" 
                      'this pattern is limited to the elements C, N, H and O only.
         Dim regEx As New RegExp
    
         Dim Matches As MatchCollection, m As Match
    
         If strPattern <> "" Then
             With regEx
                 .Global = True
                 .MultiLine = True
                 .IgnoreCase = False
                 .Pattern = strPattern
             End With
    
             Set Matches = regEx.Execute(ChemFormula)
             For Each m In Matches
                 If m.SubMatches(0) = Element Then
                     ChemRegex = IIf(Not m.SubMatches(1) = vbNullString, m.SubMatches(1), 1) 
                                 'this IIF ensures that in CH4O the C and O are count as 1
                     Exit For
                 End If
             Next m
         End If
     End Function
    
  4. Use the function like this in a cell formula

    E.g. in cell B2: =ChemRegex($A2,B$1) and copy it to the other cells enter image description here


Recognize also chemical formulas with multiple occurrences of elements like CH?OH or CH?COOH

Note that the code above cannot count something like CH3OH where elements occur more than once. Then only the first H3 is count the last is omitted.

If you need also to recognize formulas in the format like CH3OH or CH2COOH (and summarize the occurrences of the elements) then you need to change the code to recognize these too …

If m.SubMatches(0) = Element Then
    ChemRegex = ChemRegex + IIf(Not m.SubMatches(1) = vbNullString, m.SubMatches(1), 1)
    'Exit For needs to be removed.
End If

enter image description here

Recognize also chemical formulas with 2 letter elements like NaOH or CaCl?

In addition to the change above for multiple occurrences of elements use this pattern:

strPattern = "([A-Z][a-z]?)([0-9]*)"   'https://regex101.com/r/nNv8W6/2

enter image description here

  1. Note that they need to be in the correct upper/lower letter case. CaCl2 works but not cacl2 or CACL2.

  2. Note that this doesn't proof if these letter combinations are existing elements of the periodic table. So this will also recognize eg. Xx2Zz5Q as fictive elements Xx = 2, Zz = 5 and Q = 1.

    To accept only combinations that exist in the periodic table use the following pattern:

     strPattern = "([A][cglmrstu]|[B][aehikr]?|[C][adeflmnorsu]?|[D][bsy]|[E][rsu]|[F][elmr]?|[G][ade]|[H][efgos]?|[I][nr]?|[K][r]?|[L][airuv]|[M][cdgnot]|[N][abdehiop]?|[O][gs]?|[P][abdmortu]?|[R][abefghnu]|[S][bcegimnr]?|[T][abcehilms]|[U]|[V]|[W]|[X][e]|[Y][b]?|[Z][nr])([0-9]*)"
     'https://regex101.com/r/Hlzta2/3
     'This pattern includes all 118 elements up to today. 
     'If new elements are found/generated by scientist they need to be added to the pattern.
    

Recognize also chemical formulas with prenthesis like Ca(OH)?

Therefore another RegEx is needed to handle the parenthesis and multiply them.

Public Function ChemRegex(ByVal ChemFormula As String, ByVal Element As String) As Long
    Dim regEx As New RegExp
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
    End With
    
    'first pattern matches every element once
    regEx.Pattern = "([A][cglmrstu]|[B][aehikr]?|[C][adeflmnorsu]?|[D][bsy]|[E][rsu]|[F][elmr]?|[G][ade]|[H][efgos]?|[I][nr]?|[K][r]?|[L][airuv]|[M][cdgnot]|[N][abdehiop]?|[O][gs]?|[P][abdmortu]?|[R][abefghnu]|[S][bcegimnr]?|[T][abcehilms]|[U]|[V]|[W]|[X][e]|[Y][b]?|[Z][nr])([0-9]*)"
    
    Dim Matches As MatchCollection
    Set Matches = regEx.Execute(ChemFormula)
    
    Dim m As Match
    For Each m In Matches
        If m.SubMatches(0) = Element Then
            ChemRegex = ChemRegex + IIf(Not m.SubMatches(1) = vbNullString, m.SubMatches(1), 1)
        End If
    Next m
    
    'second patternd finds parenthesis and multiplies elements within
    regEx.Pattern = "(((.+?))([0-9]+)+)+?"
    Set Matches = regEx.Execute(ChemFormula)
    For Each m In Matches
        ChemRegex = ChemRegex + ChemRegex(m.SubMatches(1), Element) * (m.SubMatches(2) - 1) '-1 because all elements were already counted once in the first pattern
    Next m
End Function

This will also recognize parenthesis. Note that it does not recognize nested parenthesis.

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...