SOLVED

Addition of alphanumeric values, such as combine two or more molecular formula in one.

Copper Contributor

Hi,

I am working on a metabolomics research, where as I have some work to look for adduct formation where two different molecule can combine to form a one new molecule. So I wanted to test what two molecules are added together and forming a new compound or molecule. My question is how can I add two molecular formula in one, such as I have one molecular formula as C2H6O and other as CH4O, I wanted to add these two molecule in one that will be the new molecule and the formula will be C3H10O2.

More precisely, C2H6O + CH4O = C3H10O2

The numbers are added with appropriate character. How can I do this in excel spreadsheet. Any help will be greatly appreciated.

 

 

7 Replies

@ALAMMN 

 

You might want to search for an answer on some chemistry website. If it can be done, that would be where I'd expect a greater probability of finding out how others have done it.

 

That said, before I or anybody else tries to tackle it here, may I ask a few questions?

  1. In the very simple example you give, I notice that the sequence of the elements is exactly the same in the two basic compounds. That sequence is C-H-O, and each has a number associated with it (or the number is 1 if not shown); so the letters and numbers could be parsed out, the numbers added, and the result shown. But that's assuming they always are the same elements. Are they?
  2. The elements are also in alphabetical order (in the English alphabet); is that always true? Could they vary in some cases? If so, what are the rules for the new combination?

Those questions are just for starters. I'm sure there's a lot you're taking for granted because this is a field you're familiar with. In order to write a valid formula, one that would cover all circumstances, we'd need to know what the rules are that govern such combinations and the notations that accompany them.

 

Thank you, for your reply. @mathetes 

To answer your questions:

  1. In the very simple example you give, I notice that the sequence of the elements is exactly the same in the two basic compounds. That sequence is C-H-O, and each has a number associated with it (or the number is 1 if not shown); so the letters and numbers could be parsed out, the numbers added, and the result shown. But that's assuming they always are the same elements. Are they? 

Most organics comprise of C H O N S X (mostly X = F, Cl, Br halogens) 8 elements , yes they are always same element. So, only the number needed to add to the element. 

  1. The elements are also in alphabetical order (in the English alphabet); is that always true? Could they vary in some cases? If so, what are the rules for the new combination?

I think the order can be fixed or not fixed such as CHONSX, without alphabetic order since chlorine (Cl) denotes with two alphabet capital C and small l and bromine (Br) has B and r. Separating these letter will violate the formula. I think maintaining the order is better but the other combination should also work such as an example could be CH3Cl + C2H4 = C3H7Cl (1 not shown) or C3ClH7/ClH7C3/ClC3H7/H7C3Cl/H7ClC3  all are same. But C3H7Cl sounds more logical!

@ALAMMN 

 

As I said, those two questions only were starters. Your reply has underscored the far greater complexity involved. Certainly a level of complexity that I'm not about to try to tackle. Maybe some others will, but I'd go back to my first thought that a website devoted to chemical compounds and their combinations might be a more fruitful spot to find the solution you're seeking.

best response confirmed by allyreckerman (Microsoft)
Solution

@ALAMMN 

 

You might look around to see if there is software available (maybe even an excel add-in) that can do this.

 

In the meantime, you could try this user defined function. To view the function you would need to go into the vba editor (Alt+F11), find the workbook in the project explorer (Ctrl+R if project explorer window is not open), expand the workbook and double click on Module1. Then, you could drag/drop the module into other workbooks. To use, you would enter =chemformula(A1,B1) where your two formulas are in A1 and B1. I attached a workbook as well.

 

 

 

Public Function ChemFormula(formula1 As String, formula2 As String) As Variant
     Const elemPattern As String = "([A-Z][a-z]?)(\d+)?"
     Static regEx As Object
     Dim matches As Object
     Dim elements As Object
     Dim molecules As Long
     Dim outText As String
     Dim i As Long
     Dim dicKey As Variant
     
     
     On Error GoTo ErrHandler
     
     If regEx Is Nothing Then
          Set regEx = CreateObject("vbscript.regexp")
          
          With regEx
               .Global = True: .IgnoreCase = False: .MultiLine = False: .Pattern = elemPattern
          End With
     End If
     
     
     With regEx
          If .Test(formula1 & formula2) Then
               Set matches = .Execute(formula1 & formula2)
               Set elements = CreateObject("Scripting.Dictionary")
               
               For i = 0 To matches.Count - 1
                    With matches(i)
                         
                         If Len(.SubMatches(1)) = 0 Then
                              molecules = 1
                         Else
                              molecules = CLng(.SubMatches(1))
                         End If
                         
                         
                         If elements.Exists(.SubMatches(0)) Then
                              elements(.SubMatches(0)) = elements(.SubMatches(0)) + molecules
                                   
                         Else
                              elements(.SubMatches(0)) = molecules
                         End If
                              
                    End With
               Next i
               
               For Each dicKey In elements.Keys
                    outText = outText & dicKey
                    
                    If elements(dicKey) > 1 Then
                         outText = outText & elements(dicKey)
                    End If
               Next dicKey
                              
          Else
               Err.Raise Number:=vbObjectError + 512
               
          End If
     End With
          
          
     ChemFormula = outText
          
          
ExitProc:
     Exit Function
     
ErrHandler:
     ChemFormula = CVErr(xlErrValue)
     Resume ExitProc
     
End Function

 

 

Thank you for your help. It works perfectly!
Thank you for your response and concern. I tried JMB17' suggestion and it works for me.
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@ALAMMN 

 

You might look around to see if there is software available (maybe even an excel add-in) that can do this.

 

In the meantime, you could try this user defined function. To view the function you would need to go into the vba editor (Alt+F11), find the workbook in the project explorer (Ctrl+R if project explorer window is not open), expand the workbook and double click on Module1. Then, you could drag/drop the module into other workbooks. To use, you would enter =chemformula(A1,B1) where your two formulas are in A1 and B1. I attached a workbook as well.

 

 

 

Public Function ChemFormula(formula1 As String, formula2 As String) As Variant
     Const elemPattern As String = "([A-Z][a-z]?)(\d+)?"
     Static regEx As Object
     Dim matches As Object
     Dim elements As Object
     Dim molecules As Long
     Dim outText As String
     Dim i As Long
     Dim dicKey As Variant
     
     
     On Error GoTo ErrHandler
     
     If regEx Is Nothing Then
          Set regEx = CreateObject("vbscript.regexp")
          
          With regEx
               .Global = True: .IgnoreCase = False: .MultiLine = False: .Pattern = elemPattern
          End With
     End If
     
     
     With regEx
          If .Test(formula1 & formula2) Then
               Set matches = .Execute(formula1 & formula2)
               Set elements = CreateObject("Scripting.Dictionary")
               
               For i = 0 To matches.Count - 1
                    With matches(i)
                         
                         If Len(.SubMatches(1)) = 0 Then
                              molecules = 1
                         Else
                              molecules = CLng(.SubMatches(1))
                         End If
                         
                         
                         If elements.Exists(.SubMatches(0)) Then
                              elements(.SubMatches(0)) = elements(.SubMatches(0)) + molecules
                                   
                         Else
                              elements(.SubMatches(0)) = molecules
                         End If
                              
                    End With
               Next i
               
               For Each dicKey In elements.Keys
                    outText = outText & dicKey
                    
                    If elements(dicKey) > 1 Then
                         outText = outText & elements(dicKey)
                    End If
               Next dicKey
                              
          Else
               Err.Raise Number:=vbObjectError + 512
               
          End If
     End With
          
          
     ChemFormula = outText
          
          
ExitProc:
     Exit Function
     
ErrHandler:
     ChemFormula = CVErr(xlErrValue)
     Resume ExitProc
     
End Function

 

 

View solution in original post