Forum Discussion
Addition of alphanumeric values, such as combine two or more molecular formula in one.
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.
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
7 Replies
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?
- 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?
- 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:
- 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.
- 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!
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
