Aug 10 2021 01:49 PM - edited Aug 10 2021 01:51 PM
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.
Aug 11 2021 09:52 AM
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?
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.
Aug 11 2021 12:22 PM - edited Aug 11 2021 12:43 PM
Thank you, for your reply. @mathetes
To answer your questions:
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.
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!
Aug 11 2021 01:27 PM
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.
Aug 11 2021 04:37 PM - edited Aug 11 2021 04:44 PM
Solution
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
Aug 11 2021 06:28 PM
Aug 11 2021 06:29 PM
Aug 11 2021 07:09 PM
Aug 11 2021 04:37 PM - edited Aug 11 2021 04:44 PM
Solution
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